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
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
works just fine.
I hope this article gives you an insight in to SQL server ‘GO’ statement and potential pitfalls and new uses when creating stored procedures.