Dealing with the Dodgy GO Command

By : Subramanyam Krishna Murthy May 17, 2006

Printer friendly

The GO command plays a very important role in Microsoft SQL Server. It signals the end of a batch of SQL statements and executes them as a whole. Seems simple enough. But using GO can be disastrous if you don't understand its usage in a certain scenario.

One of the places where I found the GO command can be useful — or create havoc — is in stored procedures.

The following two examples shows the significance of GO commands in SQL Server 2000.

Create proc MyBoss
as
begin

     select 'My boss is the best'

end

select 'This is a lie'
go

Now, to test the output of MyBoss, execute the stored procedure with the following command.

execute MyBoss

The output:

-------------------
My boss is the best
-------------
This is a lie

Say, if you show this stored procedure's output to your boss, 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 because the whole code is scripted into the syscomments table until a GO command is found.

How we can use this for a better purpose?

Create proc MagicProcedure
as
begin

     select 'you can not see me next time'

end

drop proc MagicProcedure
go

Executing MagicProcedure once will allow you to execute the stored procedure and then drop it permanently. This can be used beautifully in specific needs where you want the client/customer to use the stored procedure only once to set few things on a one time basis.

Try rewriting the stored procedures, with multiple GO commands:

Create proc FunnySQLServer
as
begin

     select 'SQL server is funny'

end
go

select 'who said that?'
go

Create proc MagicProcedure
as
begin

     select 'you can not see me next time'

end
go

drop proc MagicProcedure
go

You will notice that only the queries/functions written in the stored procedures will be executed. The other queries are no longer a part of the stored procedure.

SQL Server requires a CREATE statement to appear first when coding a stored procedure. You can add any number of comments before the CREATE statement, but nothing else. So why is that executable statements can appear after a stored procedure?

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.

For example:

select 'who said that?'

Create proc FunnySQLServer
as
begin

     select 'SQL server is funny'

end
go

Will raise an error:

Server: Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.

The following example, however, will not raise an error:

--'This is to test the use of a comment before the create statement'

Create proc FunnySQLServer
as
begin

     select 'SQL server is funny'

end
go

Nor will the first example I used in this article:

Create proc MyBoss
as
begin

     select 'My boss is the best'

end

select 'This is a lie'
go

I hope this article provides some insight on how to use the GO command with stored procedures in SQL Server.

Your Name
Email	(Emails will not be displayed on the site or used for promotional purposes)
Comment
Type characters in the image	(case sensitive)