When to Use GO

[Gene Wirchenko]

Dear SQLers:

I am somewhat confused about when to use GO. I would like to
avoid it where possible, that is, I would prefer to make my batches as
big as possible. Trying to find out when GO can be omitted is a bit
of an adventure.

I have done Web searching to find an answer, but there is not
enough detail, as in, for example:
http://msdn.microsoft.com/en-us/library/ms188037.aspx

I have a script where I very early delete the database and
recreate it. In one recent problem area, I create a procedure. It
has a GO in front of it, because stored procedures have to be first in
a batch. Fine. If I then follow with a CREATE TABLE in the same
batch, I get an error that seems to indicate that the CREATE TABLE has
been included in the procedure. If I put a GO just after the
procedure, I do not have this problem.

What am I missing or misunderstanding?

(I am now putting each of my CREATE PROCEDURE statements (and the
procedure) in its own batch delimited by GO at both the beginning and
the end. This is a bit cargo cult, but it works.)

When exactly is GO required?

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
I have a script where I very early delete the database and
recreate it. In one recent problem area, I create a procedure. It
has a GO in front of it, because stored procedures have to be first in
a batch. Fine. If I then follow with a CREATE TABLE in the same
batch,

You can't. That CREATE TABLE is part of the procedure. CREATE PROCEDURE must
be the first command in the batch, and it is the only command in the batch,
a property it shares with a couple of more commands.

Again, put your source code under version-control and load the files from
a client-side program. Then you don't need to ask these questions.

[Bob Barrows]

Post by Gene Wirchenko
I am somewhat confused about when to use GO. I would like to
avoid it where possible, that is, I would prefer to make my batches as
big as possible. Trying to find out when GO can be omitted is a bit
of an adventure.

The only way I've seen to eliminate it is dynamic sql.

[rpresser]

Post by Gene Wirchenko
What am I missing or misunderstanding?

Two things. As Erland intimated, everything between the AS of CREATE PROCEDURE
and the next GO will be the procedure. See the syntax for CREATE PROCEDURE
http://msdn.microsoft.com/en-us/library/ms187926.aspx

Post by Gene Wirchenko
{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
One or more Transact-SQL statements comprising the body of the procedure.
You can use the optional BEGIN and END keywords to enclose the statements.
For information, see the Best Practices, General Remarks, and Limitations
and Restrictions sections that follow.

The BEGIN and END are *optional* (as are the semicolons). CREATE PROCEDURE
will eat everything till the next GO.

So it's not "cargo cult"; it's the defined behavior.

The second thing you're misunderstanding -- or, at least, I don't understand
your reason -- is why do you want to "make your batches as big as possible"?

If the statements you're submitting really belong together -- they should all
be executed in the same atomic transaction -- then wrap them in a
BEGIN TRANSACTION / COMMIT TRANSACTION pair. You can still put GO between them.

If the statements need to share the same variables -- then that's a reason.
DECLAREd Variables go out of scope after a GO. In that case, a semicolon may
help.

[Gene Wirchenko]

Post by rpresser

Post by Gene Wirchenko
What am I missing or misunderstanding?

Two things. As Erland intimated, everything between the AS of CREATE PROCEDURE
and the next GO will be the procedure. See the syntax for CREATE PROCEDURE
http://msdn.microsoft.com/en-us/library/ms187926.aspx

Post by Gene Wirchenko
{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
One or more Transact-SQL statements comprising the body of the procedure.
You can use the optional BEGIN and END keywords to enclose the statements.
For information, see the Best Practices, General Remarks, and Limitations
and Restrictions sections that follow.

The BEGIN and END are *optional* (as are the semicolons). CREATE PROCEDURE
will eat everything till the next GO.
So it's not "cargo cult"; it's the defined behavior.

It was cargo cult from my perspective until I found the missing
-- to me -- bit of documentation.

Post by rpresser
The second thing you're misunderstanding -- or, at least, I don't understand
your reason -- is why do you want to "make your batches as big as possible"?

It preserves variables for longer.

Post by rpresser
If the statements you're submitting really belong together -- they should all
be executed in the same atomic transaction -- then wrap them in a
BEGIN TRANSACTION / COMMIT TRANSACTION pair. You can still put GO between them.
If the statements need to share the same variables -- then that's a reason.
DECLAREd Variables go out of scope after a GO. In that case, a semicolon may
help.

That is why I want my batches as big as possible.

Sincerely,

Gene Wirchenko