Monday, 10 May 2010

GO command and semicolons in SQL Server

The GO Command

The batch terminator. The GO command is not part of the Transact-SQL language but is used by the SQLCMD, OSQL and ISQL utilities that can also be used within Query Analyzer and the Query Editor window.

A batch is a set of T-SQL statements that are submitted for execution as a group. A script is a file containing set of T-SQL statements. One script can contain many batches.

Note: if you declare variables in a script and issue a GO command the variables will ne destroyed.

The GO command is sometimes required (e.g. when defining a stored procedure – the CREATE PROCEDURE statement must be the first statement in the query batch so any preceding statements must be followed by GO).

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.MyProcedure
AS
BEGIN
 SELECT *
 FROM INFORMATION_SCHEMA.TABLES
END

SET QUOTED_IDENTIFIER OFF
GO

The Semicolon

The semicolon character is a statement terminator.

It is a part of the ANSI SQL-92 standard but was never used within Transact-SQL.

There are two situations in which you must use the semicolon:

  • Where you use a Common Table Expression (CTE), and the CTE is not the first statement in the batch.
  • Where you issue a Service Broker statement and the Service Broker statement is not the first statement in the batch.

See also: http://www.sqlservercentral.com/articles/SQL+Puzzles/thegocommandandthesemicolonterminator/2200/