Scope of GOTO with GO

The GOTO statement is used to break the normal flow of execution to a particular label specified with GOTO statement. We will see this with examples, but the question of matter is whether GOTO statement goes/switch beyond the block defined with GO statement.  

Lets checkout with an example, here we are checking a condition in “if statement”, if the condition evaluates to true then we are instructing normal flow to break and go to a particular point i.e. VKM_POINT, but in this example we define the label VKM_POINT outside the GO block, now let’s see whether the GOTO has scope beyond the block where it is used.      

declare @tempVar int

set @tempVar = 10

if @tempVar =10

      goto VKM_POINT

go

VKM_POINT:

      print 'Honey I was waiting for you'

Msg 133, Level 15, State 1, Line 5

A GOTO statement references the label 'VKM_POINT' but the label has not been declared.

So with the output we saw that GOTO is unable to switch the flow beyond the scope of batch. Lets modify the same example to have label point (VKM_POINT) inside the batch.

declare @tempVar int

set @tempVar = 10

if @tempVar =10

      goto VKM_POINT

VKM_POINT:

      print 'Honey I was waiting for you'

go

Honey I was waiting for you

Here with the output we can see that GOTO works properly when it has to jump to point which is inside the block where GOTO is used.

Conclusion: GOTO has scope only within a block

When programmer carry out rules, the ordinary human race slight.. J

Post Reference: Vikram Aristocratic Elfin Share

GO, you can also specify your own word as a batch terminator

GO is a batch terminator, you can however change it to whatever you want.

Also read previous articles on GO

GO and the Stored Procedure

GO, is it a SQL command, a jump to pointer or what exactly?

GO and the Scope of variable.

Go to Tool-> Option -> Query Execution

And then change the Batch Separator to word of your choice, here in this example, I changed it to BATCH.

Now if I try to write code with batch separators GO, SSMS will not recognize.

alter table tab2

add  d_manager varchar(10)

GO

select d_id,d_name,d_manager from tab2

Msg 102, Level 15, State 1, Line 4

Incorrect syntax near 'GO'.

Now if we replace GO with BATCH, it should work properly. Lets see

alter table tab2

add  d_manager varchar(10)

BATCH

select d_id,d_name,d_manager from tab2 

So this is how you can specify your own word as a batch terminator

Your code, sound the horn 

Post Reference: Vikram Aristocratic Elfin Share

GO and batch execution multiple times.

Go is a useful command especially when you want to execute a batch multiple times. For time being we can take an example of inserting multiple dummy records in a table, with just ONE insert statement. Also read previous articles on GO

GO and the Stored Procedure

GO, is it a SQL command, a jump to pointer or what exactly?

GO and the Scope of variable.

Let’s create a temporary table for this purpose.

use testDemo

go --First Batch

create table #tempTable

(tempID uniqueidentifier,

tempMonth int,

tempDateTime datetime )

GO -- Second Batch

Command(s) completed successfully.

These batches will just execute these separate batches for once as default value for GO is one. Lets insert 100 records in our temporary table with just one insert statement

insert into #tempTable (tempID, tempMonth, tempDateTime)

select NEWID(),(CAST(100000*RAND() AS INT) % 12) + 1 ,GETDATE()

GO 10

Batch execution completed 10 times.

“Go 100” will execute this insertion batch for ten times, and exultantly 10 records will be inserted. That’s what I call it “Power of GO”. Check this.

select * from #tempTable

tempID                               tempMonth   tempDateTime

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

C2055F4A-0FF9-4A2C-BB9F-9CC665EEC009 3           2013-03-25 14:30:37.830

4322FE78-4494-45B3-8923-5D9CD5F030CA 3           2013-03-25 14:30:37.933

86D89223-530B-423E-AAB1-8E65601D821B 8           2013-03-25 14:30:37.933

7E1A8812-9A8E-412D-8EE4-4FBE02FB4CBB 8           2013-03-25 14:30:37.937

917C89A6-1DFB-4375-A6E3-5C15F556AFCB 6           2013-03-25 14:30:37.940

7DEBDD5D-E2B9-45BB-9D65-D911DA32F431 7           2013-03-25 14:30:37.940

CC124777-7AE4-47A8-B7CA-0EE566D01465 5           2013-03-25 14:30:37.940

6A0E6F7C-47A6-4F2A-A8C0-58E4A0B498E2 4           2013-03-25 14:30:37.947

CB22E285-689A-466A-A7EB-E0734ED1435B 3           2013-03-25 14:30:37.950

7102D516-03AC-4EE9-93C8-820477E645D6 5           2013-03-25 14:30:37.953

(10 row(s) affected)

Now drop the temporary table

drop table #tempTable

Conclusion: GO can be used for multiple times batch execution.

Honk if you hate coding

Post Reference: Vikram Aristocratic Elfin Share

GO and the Stored Procedure

In this article, we will see how GO can have an effect on writing stored procedure, before that go through my previous post 

GO, is it a SQL command, a jump to pointer or what exactly?

GO and the Scope of variable.

We will directly jump into the example to see the effect of go in stored procedure, Now we are creating the stored procedure MyManager, and if you notice after end block of stored procedure, there is a select statement and a GO statement proceed with it.

Create procedure MyManager

as

begin

     select 'My Manager is the best one.'

end

select 'Oh come on! This is absolutly lie'

go

Command(s) completed successfully.

Now if we try to execute the procedure..

exec MyManager

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

My Manager is the best one.

(1 row(s) affected)

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

Oh come on! This is absolutly lie

(1 row(s) affected)

Say, if you show this stored procedure’s output to your Manager, you better have a new job lined up.

What happened? You intended to show him only the first line, but the output of the second select also is displayed.

Why? Because the whole code is scripted into the syscomments table until a GO command is found.

Now lets drop the procedure and recreate the same

drop proc MyManager

Command(s) completed successfully.

Procedure lets recreate the same procedure, where select statement appears after the GO statement.

Create procedure MyManager

as

begin

     select 'My Manager is the best one.'

end

go

select 'Oh come on! This is absolutly lie'

Now if we execute the procedure, we gets our desired result.

exec MyManager

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

My Manager is the best one.

(1 row(s) affected)

Conclusion: If the stored procedure isn’t immediately followed by a GO command, an executable statement that appears after the stored procedure will be executed together with the stored procedure, which as we have seen can have unintended consequences.

I just like to code and be happy.

Post Reference: Vikram Aristocratic Elfin Share

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