The Dodgy GO Statement

The ‘GO’ statement in SQL Server plays very important role. This can be disastrous if you don’t know its usage in certain scenario. One of the places where I found it can be useful or create havoc is in its role in stored procedures. The following two examples shows significance of SQL Server 2000 ‘GO’ statements

Tested on SQL version: Microsoft SQL Server 2000, Enterprise Edition with Service Pack 4

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 SP by following command

execute MyBoss

The output is:

------------------- 
My boss is the best

------------- 
This is a lie

Say, if you show this SP’s output to your boss, then you better have a new job :)

What happened? You were intending only to show him the first line, but the second select outputs also is displayed because the whole code is scripted in syscomments until a go statement is found.

Now, let’s see 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 SP and then drop the SP permanently. This can be beautifully used in specific needs where you want the client/customer to use code only once to set few things.

Try re-writing the same stored procedures, with GO statements like this:

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 the query/functions written in the SPs only will be executed. The other queries are no longer part of the Stored Procedure.

SQL Server developers have been forced to use the create statement as the first statement, for the creation of a stored procedure. As such we can add any number of comment statements before the create statement of a stored procedure. But at the same time I don’t understand why they allowed any executable statements after the end of stored procedure?

This is shown in the below 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.

At the same time

--‘This is to test the use of comment statement before create statement of SP’'
Create proc FunnySQLServer
as
begin
    select 'SQL server is funny'
end
go

I hope this article gives you an insight in to SQL server ‘GO’ statement and potential pitfalls and new uses when creating stored procedures.