When do I need to use Begin / End Blocks and the Go keyword in SQL Server?

Question

Can someone tell me when and where I need to use begin and end blocks in SQL Server? Also, what exactly does the Go keyword do?

Thanks in advance...

Answer 1

You need BEGIN ... END to create a block spanning more than one statement. So, if you wanted to do 2 things in one 'leg' of an IF statement, or if you wanted to do more than one thing in the body of a WHILE loop, you'd need to bracket those statements with BEGIN...END.

The GO keyword is not part of SQL. It's only used by Query Analyzer to divide scripts into "batches" that are executed independently.

Answer 2

GO is like the end of a script.

You could have multiple CREATE TABLE statements, separated by GO. It's a way of isolating one part of the script from another, but submitting it all in one block.

BEGIN and END are just like { and } in C/++/#, Java, etc.

They bound a logical block of code. I tend to use BEGIN and END at the start and end of a stored procedure, but it's not strictly necessary there. Where it IS necessary is for loops, and IF statements, etc, where you need more then one step...

IF EXISTS (SELECT * FROM my_table WHERE id = @id)
BEGIN
   INSERT INTO Log SELECT @id, 'deleted'
   DELETE my_table WHERE id = @id
END

Answer 3

GO isn't a keyword in SQL Server. It's a batch separator. GO ends a batch of statements. This is especially useful when you are using something like SQLCMD. Imagine you are entering in SQL statements on the command line. You don't necessarily want the thing to execute every time you end a statement, so SQL Server does nothing until you enter "GO".

Likewise, before your batch starts, you often need to have some objects visible. For example, let's say you are creating a database and then querying it. You can't write:

CREATE DATABASE foo;
USE foo;
CREATE TABLE bar;

because foo does not exist for the batch which does the CREATE TABLE. You'd need to do this:

CREATE DATABASE foo;
GO
USE foo;
CREATE TABLE bar;

Answer 4

GO ends a batch, you would only very rarely need to use it in code. Be aware that if you use it in a stored proc, no code after the GO will be executed when you execute the proc.

BEGIN and END are needed for any procedural type statements with multipe lines of code to process. You will need them for WHILE loops and cursors (which you will avoid if at all possible of course) and IF statements (well techincally you don't need them for an IF statment that only has one line of code, but it is easier to maintain the code if you always put them in after an IF). CASE statements also use an END but do not have a BEGIN.

Answer 5

BEGIN and END have been well answered by others.

As Gary points out, GO is a batch separator, used by most of the Microsoft supplied client tools, such as isql, sqlcmd, query analyzer and SQL Server Management studio. (At least some of the tools allow the batch separator to be changed. I have never seen a use for changing the batch separator.)

To answer the question of when to use GO, one needs to know when the SQL must be separated into batches.

Some statements must be the first statement of a batch.

select 1
create procedure #Zero as
    return 0

On SQL Server 2000 the error is:

Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.
Msg 178, Level 15, State 1, Line 4
A RETURN statement with a return value cannot be used in this context.

On SQL Server 2005 the error is less helpful:

Msg 178, Level 15, State 1, Procedure #Zero, Line 5
A RETURN statement with a return value cannot be used in this context.

So, use GO to separate statements that have to be the start of a batch from the statements that precede it in a script.

When running a script, many errors will cause execution of the batch to stop, but then the client will simply send the next batch, execution of the script will not stop. I often use this in testing. I will start the script with begin transaction and end with rollback, doing all the testing in the middle:

begin transaction
go
... test code here ...
go
rollback transaction

That way I always return to the starting state, even if an error happened in the test code, the begin and rollback transaction statements being part of a separate batches still happens. If they weren't in separate batches, then a syntax error would keep begin transaction from happening, since a batch is parsed as a unit. And a runtime error would keep the rollback from happening.

Also, if you are doing an install script, and have several batches in one file, an error in one batch will not keep the script from continuing to run, which may leave a mess. (Always backup before installing.)

Related to what Dave Markel pointed out, there are cases when parsing will fail because SQL Server is looking in the data dictionary for objects that are created earlier in the batch, but parsing can happen before any statements are run. Sometimes this is an issue, sometimes not. I can't come up with a good example. But if you ever get an 'X does not exist' error, when it plainly will exist by that statement break into batches.

And a final note. Transaction can span batches. (See above.) Variables do not span batches.

declare @i int
set @i = 0
go
print @i

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

Answer 6

I just today posted an elaborate blog post on this very topic :-). http://sqlblog.com/blogs/merrill_aldrich/archive/2009/07/24/flow-control-in-t-sql-scripts.aspx

Answer 7

after wrestling with this problem today my opinion is this: BEGIN...END brackets code just like {....} does in C languages, eg code blocks for if...else and loops

GO is (must be) used when succeeding statements rely on an object defined by a previous statement. USE database is a good example above, but the following will also bite you:

alter table foo add bar varchar(8);
-- if you don't put GO here then the following line will error as it doesn't know what bar is.
update foo set bar = 'bacon';
-- need a GO here to tell the interpreter to execute this statement, otherwise the Parser will lump it together with all successive statements.

It seems to me the problem is this: the SQL Server SQL Parser, unlike the Oracle one, is too retarded to realise that you're defining a new symbol on the first line and that it's ok to reference in the following lines. It doesn't "see" the symbol until it encounters a GO token which tells it to execute the preceding SQL since the last GO, at which point the symbol is applied to the database and becomes visible to the parser.

why it doesn't just treat the semi-colon as a semantic break and apply statements individually I don't knowm and wish it would. Only bonus I can see is that you can put a print() statement just before the GO and if any of the statements fail the print won't execute. lot of trouble for a minor gain though.