Published on

September 21, 2007

Understanding the ‘GO’ Statement in SQL Server

The ‘GO’ statement in SQL Server is a powerful tool that can be both useful and potentially disastrous if not used correctly. It plays a significant role in various scenarios, particularly in stored procedures.

Let’s explore a couple of examples to understand the significance of the ‘GO’ statement in SQL Server 2000.

Example 1: Unexpected Output

Consider the following stored procedure:

CREATE PROCEDURE MyBoss
AS
BEGIN
   SELECT 'My boss is the best'
END
SELECT 'This is a lie'
GO

If you execute this stored procedure by running the command EXECUTE MyBoss, the output will be:

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

As you can see, the second select statement is also displayed, even though it was not intended to be part of the output. This happens because the entire code is scripted in syscomments until a ‘GO’ statement is encountered.

This unexpected output can be problematic, especially if you accidentally show it to your boss. So, it’s crucial to be aware of the behavior of the ‘GO’ statement in stored procedures.

Example 2: Controlled Execution

On the other hand, the ‘GO’ statement can be used to execute specific parts of a stored procedure and then drop it permanently. This can be useful in situations where you want the client or customer to execute certain code only once to set up specific configurations.

Consider the following modified stored procedure:

CREATE PROCEDURE MagicProcedure
AS
BEGIN
   SELECT 'You cannot see me next time'
END
GO
DROP PROCEDURE MagicProcedure
GO

By executing the ‘MagicProcedure’ once, you can execute the stored procedure and then drop it permanently. This allows for controlled execution of specific code.

Similarly, you can use the ‘GO’ statement to separate different queries or functions within a stored procedure. Only the queries or functions written before the ‘GO’ statement will be executed, and the rest will be excluded from the stored procedure.

Limitations and Best Practices

It’s important to note that SQL Server developers are required to use the ‘CREATE’ statement as the first statement in a stored procedure. However, they can add any number of comment statements before the ‘CREATE’ statement.

On the other hand, executing any executable statements after the end of a stored procedure will result in an error. The ‘CREATE PROCEDURE’ statement must be the first statement in a query batch.

For example:

SELECT 'Who said that?'
CREATE PROCEDURE FunnySQLServer
AS
BEGIN
   SELECT 'SQL Server is funny'
END
GO

The above code will raise an error: ‘CREATE PROCEDURE’ must be the first statement in a query batch.

However, using comment statements before the ‘CREATE’ statement works perfectly fine:

-- This is to test the use of comment statement before create statement of SP
CREATE PROCEDURE FunnySQLServer
AS
BEGIN
   SELECT 'SQL Server is funny'
END
GO

In conclusion, understanding the ‘GO’ statement in SQL Server is crucial for avoiding unexpected output and utilizing its potential benefits in stored procedures. By using it wisely, you can control the execution of specific code and ensure the desired outcomes.

Stay tuned for more SQL Server tips and tricks!

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.