SQL Server A to Z – GO


GO.  Such a simple word.  So fraught with meaning.

OK, not really.

The GO command isn’t a T-Sql statement, but rather a command that tools like SSMS and OSQL recognize as a batch delimiter.  The tool sees GO and knows it should send all the preceding statements since the last GO (or since the beginning of the code, if there isn’t a preceding GO) to the engine as a batch.  SQL Server will compile all the statements in the batch into a single execution plan.

Where this becomes important to remember is when you’re working with variables.

For example

USE AdventureWorks;
GO

DECLARE @FirstName VARCHAR(50)
DECLARE @LastName VARCHAR(50)

SELECT @FirstName = FirstName FROM Person.Contact WHERE ContactID = 15696
PRINT @FirstName
GO

In the example above, you have 2 batches. The first batch is just the USE statement. The second declares two variables, SELECTs into one of them and then PRINTs the value. Simple. But what if we were to add a couple more statements after the GO?

USE AdventureWorks;
GO

DECLARE @FirstName VARCHAR(50)
DECLARE @LastName VARCHAR(50)

SELECT @FirstName = FirstName FROM Person.Contact WHERE ContactID = 15696
PRINT @FirstName
GO

SELECT @LastName = LastName FROM Person.Contact WHERE ContactID = 15697
PRINT @LastName
GO

If we were to run this, we’d get an error:

Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable “@LastName”.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable “@LastName”.

Why? Because the @LastName variable was defined within the scope of the second batch. The last set of SELECT and PRINT statements are outside this scope and therefore cannot access the variables.

The GO statement can accept one integer parameter, which tells SQL Server how many times to execute the preceding batch of statements.

USE AdventureWorks;
GO

DECLARE @FirstName VARCHAR(50)
DECLARE @LastName VARCHAR(50)

SELECT @FirstName = FirstName FROM Person.Contact WHERE ContactID = 15696
PRINT @FirstName
GO 5

And that’s GO.

Also recommended:

Leave a comment

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>