Tuesday, August 11, 2009

Using the "GO" Command in SQL Server

This post discusses the "GO" command in SQL Server.
Before proceeding with explaining GO, it is first necessary to understand what a "Batch" is.

As described in MSDN BOL, a Batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution. SQL Server compiles the statements of a batch into an execution plan.

GO is a command used for signaling the end of a batch. Note that it is not a T-SQL statement.
This command is recognized by the following SQL Server utilities:
  • sqlcmd
  • osql
  • SQL Server Management Studio Code editor
Using GO, you can specify multiple batches within your T-SQL code.
So, for an example, instead of having different files containing T-SQL code and separately executing each of it, you can just use a single file/code and separate the batches using GO. This is useful in many cases.

An example of some T-SQL code (in SQL Server Management Studio Code Editor) featuring batches is the following:

------------ Example 1------------
--BATCH 1
USE [AdventureWorks]
GO

--BATCH 2
DECLARE @STRING1 AS VARCHAR(50)
SET @STRING1='BATCH 2'
SELECT @STRING1 AS RESULT
GO

--BATCH 3
DECLARE @STRING2 AS VARCHAR(50)
SET @STRING2='BATCH 3'
SELECT @STRING2 AS RESULT
GO
---------------------------------

The above T-SQL code features three batches.
The first batch instructs SQL Server Database Engine to use the "AdventureWorks" database.
The second batch declares a varchar variable, sets a value for it, and prints its value, and the third one does exactly the same with another variable.

So, in this example, the SQL Server Management Studio Code Editor will separately send the three bathes to SQL Server Database Engine for processing.

Note that by the time there are no dependencies between the batches, the above code is executed without any problems.

Now, consider the following code:

------------ Example 2------------
--BATCH 1
USE [AdventureWorks]
GO

--BATCH 2
DECLARE @STRING1 AS VARCHAR(50)
DECLARE @STRING2 AS VARCHAR(50)

SET @STRING1='BATCH 2'
SET @STRING2='BATCH 3'

SELECT @STRING1 AS RESULT
GO

--BATCH 3
SELECT @STRING2 AS RESULT
GO
---------------------------------

Again, the above code features three batches. Though you can see that the variable @STRING2 is declared in Batch 2 but the SELECT statement from within Batch 3 also tries to access it for printing its value. Though, by the time @STRING2 is defined in Batch 2, consequently it will be only available for T-SQL Statements belonging to the scope of Batch 2.

Any other T-SQL statement trying to access it will result to a runtime error because @STRING2 is only visible within Batch 2.

Based on the above example, if you try to execute the entire T-SQL code you will get the following error message:

Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@STRING2".

GO is very useful in SQL Server. Though, you need to use it only when is really needed.
So, you need to keep in mind that along with defining a batch using the GO command, you define the scope of that specific piece of T-SQL code.

I'm reading: Using the "GO" Command in SQL ServerTweet this!

3 comments:

Allan said...

Is it possible to insert some special character into the SQL Statement to allow for the GO Command to be on a single line?

I need to place my sql statements into a single excel cell to have them run properly with an interface I use, and the statements use a go command.

Artemakis Artemiou [MVP] said...

Hi Allan,

Unfortunately this is not possible because the GO command is not a T-SQL statement but rather a command which signals the end of a batch of Transact-SQL statements to the SQL Server utilities.

From MSDN BOL: "A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments."

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

Cheers,
Artemakis

azuremarine said...

The behavior of "RETURN" is worth noting as well.

In SSMS, "RETURN" works within a batch when executing code on screen, but then SSMS will continue executing subsequent batches.

I believe this behavior also extends into SQLCMD and the ":R" command, where it becomes a bit more confusing. This is because there may well be more than one "batch" within a single ":R" file. I believe RETURN will still only "return" from a single "inner" batch; it will not return from the ":R" file as a whole.

I've seen some posts on other forums where people believed that RETURN "does not work", but it actually DOES work, but only with a batch.

It would be useful to have a "STOP" command for SQLCMD and SSMS; apparently some other tools have such a capability.