Using "KILL" in a stored procedure.

Question

Post reply

Using "KILL" in a stored procedure.

Anoop P.

SSCommitted

Points: 1632
More actions
March 23, 2005 at 5:24 am

#65835

HI,
I want to use the KILL command inside a procedure.
I am getting the required spid in an int variable (@spid).
But its giving me error when I try to fire
exec (KILL @spid)
or EXECUTE sp_executesql ('kill ' + convert (varchar(10),@spid) ).
The error message is as follows.
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near 'kill '.
Could anybody please help me understand where the problem lies.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Anoop P. SSCommitted Points: 1632 More actions · Answer 1

Hi Somebody told me that you cannot calla stored procedure inside another. Is this true ?

If I wont be able to call KILL command from a stored procedure, can I do it from a script ? If yes, how ?

I tried calling the same from a script; but its not working with the above mentioned options.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

The following works for me

DECLARE @id NVARCHAR(6)

DECLARE @stmt NVARCHAR(20)

SET @ID = 53

SET @stmt = 'KILL '+ @ID

EXEC sp_ExecuteSQL @stmt

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You can call any amount of stored procs while you're in a stored proc. The only thing you have to watch for is the nesting level.

call sp1 : nesting level = 1

sp1 calls sp2 : nesting level = 2

sp2 calls sp1 : nesting level = 3 >> but you just started a recursinve algorithm. After you reach 32, you won't be able to call anything else that will increment the nesting level.

however if you have a sp like this

create proc spa

as

exec spb

exec spc

exec spd

exec spe

the max nesting level you will reach is 2 (assuming none of the 4 sps will call other sps).

Exec () cannot resolve the statement in the parenthesis, you must set it into a variable and use it like so :

Declare @ExecSQL as varchar(100)

SET @ExecSQL = 'KILL ' + CAST("Your spid goes here" As Varchar(10))

exec (@ExecSQL)

Anoop P. SSCommitted Points: 1632 More actions · Answer 4

Thanks Frank & Remi.

Your suggestions helped me solve my problem.

I am still wondering why

exec ( 'kill ' + convert(varchar(10),@spid) ) is not working where as exec( @ExecSQ) works fine !!!

I find that the string value inside the paranthesis is the same in both the cases !!! Why do you think this is happening ?

Thanks once again for your help. 🙂

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

quoting myself :

Exec () cannot resolve the statement in the parenthesis, you must set it into a variable and use it like so ...

I don't know why it's like that but it's like that... Just one more gotcha.