Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Dodgy GO Statement

By Subramanyam Krishnamurthy,

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
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.

Total article views: 11475 | Views in the last 30 days: 5
 
Related Articles
FORUM
FORUM

select statement

select statement

FORUM

Execute Stored Procedure with Select Statement as Input Parameters

Execute Stored Procedure with Select Statement as Input Parameters

FORUM

Allocate memory to SELECT statement in sql server 2005

Allocate memory to SELECT statement in sql server 2005

FORUM

select statements

select statements

Tags
sql puzzles    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones