Loosing variable name in stored procedure

[transparent]

The following code works fine:


CREATE PROCEDURE usp_news_delete

@news_id int

AS


-- Remove News/Document association
DELETE FROM tbl_document_news_intersect where news_id=@news_id

-- Remove News/Category Intersect
DELETE FROM tbl_news_category_intersect where news_id=@news_id
GO

However the following returns the error "Error 137: Must declare the variable @news_id"


CREATE PROCEDURE usp_news_delete

@news_id int

AS


-- Remove News/Document association
DELETE FROM tbl_document_news_intersect where news_id=@news_id

-- Remove News/Category Intersect
DELETE FROM tbl_news_category_intersect where news_id=@news_id
GO

-- Remove News/Department Intersect
DELETE FROM tbl_news_department_intersect where news_id=@news_id
GO

-- Remove News
DELETE FROM tbl_news where news_id=@news_id
GO


Its as if the stored procedure 'forgets' the name news_id variable after two uses!

Any ideas whats going wrong?

 

[jester777]

The problem is caused by the GO command. The GO command ends a batch and unassigns user variables. Do you really need the GO's?
The following is from Books Online

Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.

USE pubs
GO

DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'

GO -- @MyMsg is not valid after this GO ends the batch.

-- Yields an error because @MyMsg not declared in this batch.

PRINT @MyMsg

GO

 

[tlbroadbent]

Further info. The stored procedure ends with the first GO. Thus the SP is created with only the first two delete statments. The last two deletes are executed immediately following the creation of the SP. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[denzillobo]

Why are you using the GO command?
try exec instead

DENZIL DENZIL

 

[tlbroadbent]

Why use EXEC? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.