SQL Server states i must declare my input variables!

[MartinF]

Hi all,

Im really stumped with this...im new to T-SQL and trying to write a stored procedure. This is my code:

CREATE PROCEDURE SetStrandState

@pCableName varchar(50),
@pStartStrand int,
@pEndStrand int,
@pStrandState int,
@pComment varchar(80),
@pUser varchar(25)

AS

DECLARE
@OutputMsg varchar(255),
@CurrentStrand int,
@StrandStateDesc varchar(25),
@StartStrand int,
@EndStrand int

select @StrandStateDesc = dbo.state.condition from dbo.state where dbo.state.stateid=@pStrandState
GO

set @StartStrand = @pStartStrand
set @EndStrand = @pEndStrand

...the whole thing so far will parse except last two lines, i get messages saying pStartStrand & pEndStrand must be declared!

What am i doing wrong? Im on SQL server 2000 also.

Thanks

 

[MartinF]

I have figured it...

...it was the GO keyword, perhaps i must research its use a bit more fully. It appeares to cancel out the variable declaration! Should i even use it at all within my SP?

 

[gmmastros]

GO is not part of the TSQL language. Instead, it is a special command recognized by the SQL Server Management Studio. This command indicates the end of a batch.

You can think of it as having separate query windows for each batch. One batch of statements doesn't know anything about other batches of statements.

You can also think of GO as the "end" of your stored procedure declaration.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

From BOL - The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command

GO isn't a TSQL command. It marks the end of a batch in Query Analyzer and therefore signals the end of a stored procedure definition in that batch, so it cannot be part of an SP.

Simi

 

[MartinF]

Thanks guys, wont be using GO in my SP's again anytime soon! ...i must admit its use has always evaded me even in query analyser, but now i understand.

 

[gmmastros]

There are some things that require a GO in query analyzer. For example, creating multiple stored procedures.

Ex:

Create Procedure TestA
As
Select 'A' As Blah

Create Procedure TestB 
As
Select 'B' As Blah

Drop Procedure TestA
Drop Procedure TestB

If you run the code above in a query window, you will get an error. But... if you put GO's in there, you'll be fine.

Create Procedure TestA
As
Select 'A' As Blah

GO

Create Procedure TestB 
As
Select 'B' As Blah

GO

Drop Procedure TestA
Drop Procedure TestB

My advice is to NOT use GO unless you really need to, where you would get an error without it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom