GO statement in SQL

In this blog, we’ll discuss about why not to use the ‘GO’ statement inside Stored Procedure or Functions or Views in Microsoft SQL Server.

The GO statement in MS SQL act as a releases resources and clean out your session.GO statement is used as a Batch separator in Sql Server. Batch is nothing but one or more Sql Server statements sent to the Sql Server engine as one set of statements.

So,once the GO statement is used inside stored procedure or function or view, it’ll make a batch of all the statements from the beginning of the script upto the GO statement will be compiled into one execution plan and sent to the server for process.So, it means everything in that batch is local to that batch. In other words everything after the GO statement is treated as different then the rest of the part above the GO statement.

Example1

CREATE PROCEDURE GO_Test1
AS
SELECT ‘First Statement’
GO
SELECT ‘Second Statement’
SELECT ‘Third Statement’

EXEC GO_Test1
–Output
First Statement

Now, check your procedure definition, you’ll be find that, the procedure definition is exactly upto the last statement above the GO statement.
CREATE PROCEDURE GO_Test1
AS
SELECT ‘First Statement’

Example2

CREATE PROCEDURE GO_Test1
AS
Begin
SELECT ‘First Statement’
GO
SELECT ‘Second Statement’
SELECT ‘Third Statement’
End

On executing the above query,you’ll get below SQL error message.

Msg 102, Level 15, State 1, Procedure GO_Test1, Line 4
Incorrect syntax near ‘First Statement’.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘End’.

The GO statements that you see in other’s scripts are to prevent the parser from stop execution after the previous statement errors out. it’s similar to Visual Basic “On Error Resume Next” statement. that way your script will continue execution until the end of the script file.

In your stored proc or functions or views, you don’t need the go statement.

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s