The GO Command and the Semicolon Terminator

One of the new syntax requirements for SQL Server 2005 is the use of semicolons in certain situations. Many T-SQL programmers are unfamiliar with how to use semicolons since they were never used in earlier editions of SQL Server. This article is a brief overview of how to use semicolons and how their use differs from the GO command most T-SQL programmers have used.

The GO Command

“GO” is a batch terminator. Technically speaking, the GO command is not even a part of the Transact-SQL language. It is really a command used by the SQLCMD, OSQL and ISQL utilities that can also be used within Query Analyzer and the Query Editor window.

NOTE: A batch should not be confused with a script. A batch is a set of T-SQL statements that are submitted for execution as a group. A script is simply a file containing set of T-SQL statements. One script can contain many batches.

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. Indeed, it was possible to code T-SQL for years without ever encountering a semicolon.

Usage

There are two situations in which you must use the semicolon. The first situation is where you use a Common Table Expression (CTE), and the CTE is not the first statement in the batch. The second is where you issue a Service Broker statement and the Service Broker statement is not the first statement in the batch.

Here is an example of a Service Broker command:

USE SQLExamples
DECLARE @ConversationHandle uniqueidentifier;
BEGIN TRANSACTION;
BEGIN DIALOG CONVERSATION @ConversationHandle
FROM SERVICE [ResponseService]
TO SERVICE 'RequestService'
ON CONTRACT [Contract]
WITH LIFETIME = 600, ENCRYPTION = ON
;  -- I must use the semicolon terminator here so that the following SEND command will be the first word in the command
-- Using GO here would destroy the variable I am using to pass a value to the statement
SEND ON CONVERSATION @ConversationHandle
MESSAGE TYPE [GetRecord]
(<123456789>);
COMMIT;

There are also situations in which the GO command is required.

Consider a script used to create a stored procedure:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.uspTestProc1
AS
BEGIN
SELECT *
FROM INFORMATION_SCHEMA.TABLES
END
-- The stored procedure is created
/*
Command(s) completed successfully.
*/SET QUOTED_IDENTIFIER OFF
GO

What would happen if I substituted semicolons for the GO commands in this script?

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
CREATE PROCEDURE dbo.uspTestProc1
AS
BEGIN
SELECT *
FROM INFORMATION_SCHEMA.TABLES
END
-- The attempt to create the stored procedure fails
/*
Msg 111, Level 15, State 1, Procedure uspTestProc1, Line 12
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch
*/SET QUOTED_IDENTIFIER OFF;

Sometimes, it is unclear whether the GO command or semicolon is the better choice for a script.

Consider this example:

/* Example 1: Using semicolons */USE AdventureWorks;
DECLARE @StartProductID int
DECLARE @CheckDate datetime
SELECT @StartProductID = 972
SELECT @CheckDate = '2004-04-04'
EXEC dbo.uspGetBillOfMaterials @StartProductID, @CheckDate;
-- Now I’ll switch databases ...
USE model;
-- ... and then switch back to the AdventureWorks database
USE AdventureWorks;
-- Now I’ll change the values of the variables
-- I’m still working within the same batch, so I can assign new values to the variables I created earlier in the script
SELECT @StartProductID = 400
SELECT @CheckDate = '2000-08-08'
EXEC dbo.uspGetBillOfMaterials @StartProductID, @CheckDate;

Notice how I switched back and forth between two different databases and yet I was still able to assign new values to the variables I created at the beginning of the script.

Now let’s try the same script with the GO command substituted for the semicolons:

/* Example 2: Using GO command */USE AdventureWorks
GO
DECLARE @StartProductID int
DECLARE @CheckDate datetime
SELECT @StartProductID = 972
SELECT @CheckDate = '2004-04-04'
EXEC dbo.uspGetBillOfMaterials @StartProductID, @CheckDate
GO
-- By executing a GO command, I have ended the batch.  The variables I created have been destroyed.
-- Now I’ll switch databases ...
USE model
GO
-- ... and then switch back to the AdventureWorks database
USE AdventureWorks
GO

-- Now I’ll change the values of the variables
SELECT @StartProductID = 400
SELECT @CheckDate = '2000-08-08'
-- The variables no longer exist, so my attempt to assign values to these variables fails with the following messages:
/*
Msg 137, Level 15, State 1, Line 3
Must declare the scalar variable "@StartProductID".
Msg 137, Level 15, State 1, Line 4
Must declare the scalar variable "@CheckDate".
*/EXEC dbo.uspGetBillOfMaterials @StartProductID, @CheckDate
-- The attempt to execute the stored procedure also fails
/*
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@StartProductID".
*/

Using semicolons in this script saves me a lot of coding, since I am executing the same commands with different parameter values.

I hope this article has cleared up some of the confusion about the GO command and the semicolon terminator. Feel free to ask me additional questions if you would like more information.