Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The GO Command and the Semicolon Terminator

By Ken Powers, (first published: 2006/01/09)

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.

Total article views: 40161 | Views in the last 30 days: 33
 
Related Articles
FORUM

RE: The GO Command and the Semicolon Terminator

Thanks for the article. Clear and consice examples...Thanks. I have encountered some problems with...

FORUM

The USE command and variables

Trying to use the USE command with a variable

FORUM

Using SQL command from variable in Data flow task

Issue using SQL command from variable in OLEDB Source

FORUM

Why is there no SQL Command using Variable in ADO NET Source?

SSIS 2008 - SQL Command using Variable in ADO NET Source

FORUM

Sql command not taking the packagevariable value

Sql command not taking the package variable value

Tags
sql puzzles    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones