Error in stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error in stored procedure

Author

Topic

Mighty Quinn
Starting Member

5 Posts

Posted - 2008-05-21 : 21:39:59

Hi All,

I have a problem with the procedure shown below. When I try saving it I get the following error, can anyone help

Msg 102, Level 15, State 1, Procedure KillPurveyanceProcess, Line 19
Incorrect syntax near '@tSPId'.

ALTER PROCEDURE [dbo].[KillPurveyanceProcess]
AS

--SET NOCOUNT ON;

DECLARE @tSPId smallint;
DECLARE @tblocked smallint;

DECLARE my_cursor CURSOR FOR SELECT spid,blocked from sys.sysprocesses where program_name ='PurveyanceImport'
and status = 'suspended' and cmd = 'UPDATE'

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @tSPId,@tblocked

WHILE @@FETCH_STATUS = 0
BEGIN

Kill @tSPId

FETCH NEXT FROM my_cursor INTO @tSPId,@tblocked
END

CLOSE my_cursor
DEALLOCATE my_cursor

Thanks in Advance
Neil

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-21 : 22:57:19

Kill doesn't take parameter, need dynamic sql for that.

Mighty Quinn
Starting Member

5 Posts

Posted - 2008-05-22 : 01:04:53

Thanks rmiao, I've created the SQL on the fly for the Kill command, the stored proc now saves, I now need to wait to for jobs to meet the select criteria. I will get back to this post on the results.

Thanks once, could not find any reference to the need to create the SQL on the fly for the Kill command.

Neil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources