Using KILL with a declared variable

Question

I'm trying to use a KILL statement with a declared variable but it's giving me a syntax error. Is there anyway to not use a constant and programatically change the SPID?

For example:

DECLARE @SPID smallint
SET @SPID = 100
Kill @SPID

BTW this is just an example. I need to run the kill in a loop with a cursor to get rid of old persistant user connections. (Don't ask)

Answer 1

I think you're going to need dynamic SQL for this. Read this essential page before doing anything with dynamic SQL, please.

DECLARE @SPID smallint
DECLARE @SQL nvarchar(1000)

SET @SPID = 100

SET @SQL = 'KILL ' + CAST(@SPID as varchar(4))

EXEC (@SQL)

Answer 2

You could always do this via SMO as well, using powershell or C# :

http://msdn.microsoft.com/en-gb/library/microsoft.sqlserver.management.smo.server.killprocess(v=sql.110).aspx

This kind of management of sql server and the code that goes with it is just what powershell and SMO are good at, and T-SQL can sometimes be fiddly with.