The GO statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

The GO statement

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-06-06 : 14:01:02

I know that the "GO" keyword in SQL Server groups a set of commands into a batch. Now, I'm maintianing a db at the moment and it seems this person is using this keyword a lot (pretty much after every SQL statement) and it looks like this:

SELECT <expression>
<stuff>
GO

SELECT <expression>
<stuff>
GO

And so on. When should/shouldn't I be using the GO statement. Do I use it if I'm doing multiple queries (more than 10) or are there mission critical times when it needs to be used? I'm lost, little help.
--Nick

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-06 : 14:28:30

GO
Signals the end of a batch of Transact-SQL statements to the Microsoft® SQL Server™ utilities.

Syntax
GO

Remarks
GO is not a Transact-SQL statement; it is a command recognized by the osql and isql utilities and SQL Query Analyzer.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. SQL Query Analyzer and the osql and isql command prompt utilities implement GO differently. For more information, see osql Utility, isql Utility, and SQL Query Analyzer.

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

Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.

USE pubs
GO
DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'
GO -- @MyMsg is not valid after this GO ends the batch.

-- Yields an error because @MyMsg not declared in this batch.
PRINT @MyMsg
GO

SELECT @@VERSION;
-- Yields an error: Must be EXEC sp_who if not first statement in
-- batch.
sp_who
GO

SQL Server applications can send multiple Transact-SQL statements to SQL Server for execution as a batch. The statements in the batch are then compiled into a single execution plan. Programmers executing ad hoc statements in the SQL Server utilities, or building scripts of Transact-SQL statements to run through the SQL Server utilities, use GO to signal the end of a batch.

Applications based on the DB-Library, ODBC, or OLE DB APIs receive a syntax error if they attempt to execute a GO command. The SQL Server utilities never send a GO command to the server.

Permissions
GO is a utility command that requires no permissions. It can be executed by any user.

Examples
This example creates two batches. The first batch contains only a USE pubs statement to set the database context. The remaining statements use a local variable, so all local variable declarations must be grouped in a single batch. This is done by not having a GO command until after the last statement that references the variable.

USE pubs
GO
DECLARE @NmbrAuthors int
SELECT @NmbrAuthors = COUNT(*)
FROM authors
PRINT 'The number of authors as of ' +
CAST(GETDATE() AS char(20)) + ' is ' +
CAST(@NmbrAuthors AS char (10))
GO

See Also

Batches

Batch Processing

Writing Readable Code

iamlerxt
Starting Member

11 Posts

Posted - 2006-06-06 : 17:05:57

The main advantage (and sometimes disadvantage) I've found with GO is that it does in fact break up your statements into individual segments. If you have 5 queries with a GO between each one, they run independently. If you have some sort of runtime error in query #5, queries 1-4 will still run. Without the GO statements, any error encountered anywhere in your queries will prevent the ENTIRE thing from running.

Also, you cannot use GO with IF BEGIN ELSE transact sql. I'm not really sure why this is.

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-06-06 : 19:18:17

Cool, thanks for the information.
--Nick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources