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!