GO and the Scope of variable.

We  will be see the scope of a local variable and it affect with the GO statement, before that go through my previous post  GO, is it a SQL command, a jump to pointer or what exactly?

At the present let’s discuss about the scope of a local variable, by execute the following set of t-SQL statements

declare @strMsg varchar(25)

set @strMsg = 'Testing scope with go'

select @strMsg as GoMessage

GoMessage

-------------------------

Testing scope with go

(1 row(s) affected)

We can see, it executed properly and the value of the local variable @strMsg which was set, displays accurately with select statement.  

Now let’s add GO statement in between set and select statement and see the result. Will it make any difference let’s see

declare @strMsg varchar(25)

set @strMsg = 'Testing scope with go'

go

select @strMsg as GoMessage

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@strMsg".

Here to our shock we found error saying @strMsg not declared… :-O, How come, we have already declare and set @strMsg before displaying it, then what is the problem?

Reason: Encounter of GO statement  send signal to to Sql Server instance so to send the current batch  (T-SQL statements between  GO commands)  and execute Independently.

This means, the satement before go will form a batch and execute independently and the statement after go to the next encounter go will form the next batch and executes seperatly.

In our example, 2 batches gets created and executes seperately

First batch is this, which executes independently

declare @strMsg varchar(25)

set @strMsg = 'Testing scope with go'

where as, Second batch is this, which ssms sent as a independent execution.

select @strMsg as GoMessage

So due to independent execution of second batch, it throws error, because we are querying a string value, without declaring it.

Wrapping up : GO is not a Transact-SQL statement; it’s just a command to send signal so to send the current batch  (T-SQL statements between two GO commands) of T-SQL statements to Sql Server instance.

A fair code ought to be pursued by the deed in silence.

Post Reference: Vikram Aristocratic Elfin Share