SQL – How to use (not use) GO within Batches

“GO” is not a T-SQL statement, it’s a command specific to utilities such as SQLCmd, OSQL, Query Analyzer and SQL Server Management Studio.  Go is not part of ANSI-SQL or Transaction-SQL (TSQL).  The GO command is interpreted by these tools to trigger a batch of Transaction-SQL.

 

In the past I’ve ran into problems using GO when creating scripts that are used for moving data or structure to another database.  Usually these scripts include multiple Creates, Alters, Inserts DROP and etc.  A bad habit I had was to add the GO command after every statement, which is not needed.  A caveat pertaining to Stored Procedures, in a script that contains multiple statements, a GO command must be the last line of the SP.  If a GO command is not include as the last line of the SP then all then the code following the SP will be compiled and save in the SP.  I’ve seen this problem many times when a person creates a Stored Procedure and in the same script add permissions to that SP without a GO command separating the two statements. Example:

 

 

CREATE PROCEDURE DoSomeThing

  @Var1 VARCHAR(50)

AS

  SELECT TOP 10 Col1, Col2

  FROM Table

  WHERE Col2 = @Car1

–GO –Go is needed hter

GRANT EXECUTE ON DoSomeThing TO User

 

Now every time the DoSomeThing SP is executed the “Grant Excute…” statement will be ran also.

 

Also the scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.

 

 

 

Resources:

SQL Server Books -ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/b2ca6791-3a07-4209-ba8e-2248a92dd738.htm

http://msdn.microsoft.com/en-us/library/ms188037.aspx

 

SQL Server batch rules

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1161826,00.html

 

Errors in T-SQL Batches and the GO Statement in ETL process

http://www.siusic.com/wphchen/errors-in-t-sql-batches-and-the-go-statement-in-etl-process-63.html

 

IF and TRANSACTION problem

http://www.eggheadcafe.com/software/aspnet/32705839/if-and-transaction-proble.aspx

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *