sp_executesql question

[husker95]

I am trying to use the Kill command dynamically in a scheduled job. I have built a small script that does the following:

SET @ParmDefinition = N'@pid smallint'

-- load up SQLString once
SET @SQLString = N'KILL @pid'

..................

-- kill the process
EXECUTE sp_executesql @SQLString, @ParmDefinition, @pid = @spSpid

Note, @spSpid (smallint as well) is loaded up in a cursor that selects problem processes that need to be killed. I cannot get this to work. Am i missing something or is this unpossible? Thanks in advance.

[eralper]

Hi,

Unfortunately KILL command can not be used this way. KILL is an exception in the syntax that you are trying to use.

But you can use KILL like below, declare variables once.

DECLARE @SQLString nvarchar(255)
DECLARE @SPID smallint

Then in the loop each time, you should run the below code statements

SET @SPID = 52
SET @SQLString = N'KILL ' + CAST(@SPID AS CHAR(5))
EXEC(@SQLString)