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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

After wrestling with this problem today my opinion is this  BEGIN   END brackets code just like        does in C languages  e g  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 unable 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 know 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

User · Answer

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

User · Answer

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

[sql-server] When do I need to use Begin / End Blocks and the Go keyword in SQL Server?

Examples related to sql-server

Examples related to tsql

Examples related to language-features