none
Use of GO in a Stored Procedure RRS feed

  • Question

  • Hello All

    Getting back to basics again!

    I have an SQL statement that is broken down into various sections, as it drops a temp table, then populates it, then generates another temp table based on the first one, then drops the first temp table and so on.......it enables me to execute all the code in one go.

    I end each statement/block with a GO clause, to ensure it finishes running before the next peice of code.

    However, when I try to incorprate this into a stored procedure, I cannot use the GO command. If I remove all the GO lines, the sp will run, but can I be confident that it's running how it should, and that the lines of code near the end will not begin to execute before the lines above it have?

    I've used a semicolon at the end of each statement in the sp?

    Many thanks
    Leeroyster

    Friday, August 20, 2010 2:35 PM

Answers

  • > So if you include "GO" in the middle of a sproc definition, SQL Server interpretes the preceding code as the definition and errors out on both parts or only on the remainder.

    No, this is incorrect. The query interface sees the GO, and sends the code between two GO as a batch to SQL Server. SQL Server never sees the GO.

    It is sometimes forgotten, or users are not aware of it, but SQL Server runs as server and you never communicate with directly. You always communicate through a client program.

    And while SQL Server Management Studio ships with SQL Server, it is not part of SQL Server as a program, but is a separate program which communicates with SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Sunday, August 22, 2010 6:16 PM
  • You cannot call script files from stored procedures. As I said two years ago, SQL Server is a server application - and SQLCMD is a client.

    It's a little confusing because you seem to use DML to mean DDL. (DML is Data Manipulation Language, that is INSERT, UPDATE, DELETE and also SELECT according to some, while DDL is Data Definition Language, that is CREATE/ALTER TABLE etc.)

    Yes, if you want to interleave DDL and DML, you will need to make sure that the DML is not compiled until the DDL it depends on has been executed. Three solutions:

    1) Separate batches. (Don't forget to check @@trancount in the beginning of each batch.)=
    2) But most commands in EXEC().
    3) Have a program (in C# or whatever) to run the whole thing.

    My preference is for the latter.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 30, 2012 10:17 PM
  • As you suggest, you can't use the GO statement within a stored procedure. The GO command indicates the end of a batch of SQL Statements and a stored procedure is itself a batch of statements encapsulated as one routine.

    You can be confident that each statement will run sequentially as this is the behaviour of SQL Server. So statements will not start until the previous statement has completed.

     

    HTH!


    every day is a school day
    Friday, August 20, 2010 2:58 PM
    Moderator
  • It should also be mentioned that GO is a batch terminator, as Rich has said; however, you can use a command terminator in your code to signify the end of a command.  The command terminator is the semicolon (;).  You can put ; at the end of a command to let the optimizer know it is complete and this is a very good programming practice. 

    It has even been mentioned that SQL Server may at some point require the use of command terminators, but we will just have to wait and see.


    http://jahaines.blogspot.com/
    Friday, August 20, 2010 3:03 PM
    Moderator
  • "GO" is not part of T-SQL, it is something related to the utilities. That is why you can not use "GO" inside your sp to indicate end of the batch. In T-SQL you use BEGIN ... END to delimit blocks.

    For example, this script will fail, even though the batch looks ok. Why?

    Because "GO" is not a T-SQL command.

    USE tempdb;
    GO
    DECLARE @sql nvarchar(4000);
    
    SET @sql = N'create table #T(c1 int)' + CHAR(10) + N'GO' + CHAR(10) + N'drop table #T' + CHAR(10) + N'GO';
    
    PRINT @sql;
    
    EXEC sp_executesql @sql;
    GO
    

    It is recognized by the utilities like SSMS / query window, but it is not sent to the engine as part of the batch.

    AMB

    Friday, August 20, 2010 3:33 PM
    Moderator

All replies

  • GO Statement will not harm your SP at all.

    and you can use SP without GO Statement also.

    Are you getting any error? If yes, please paste it here

     

     

    Also, you can read this discussion.

    http://bytes.com/topic/sql-server/answers/82251-go-used-stored-procedure

    • Edited by Nishit Badani Friday, August 20, 2010 2:48 PM Link Added for an Article
    • Marked as answer by Leeroyster Saturday, August 21, 2010 9:13 AM
    • Unmarked as answer by Kalman TothModerator Sunday, August 22, 2010 3:42 PM
    Friday, August 20, 2010 2:45 PM
  • As you suggest, you can't use the GO statement within a stored procedure. The GO command indicates the end of a batch of SQL Statements and a stored procedure is itself a batch of statements encapsulated as one routine.

    You can be confident that each statement will run sequentially as this is the behaviour of SQL Server. So statements will not start until the previous statement has completed.

     

    HTH!


    every day is a school day
    Friday, August 20, 2010 2:58 PM
    Moderator
  • It should also be mentioned that GO is a batch terminator, as Rich has said; however, you can use a command terminator in your code to signify the end of a command.  The command terminator is the semicolon (;).  You can put ; at the end of a command to let the optimizer know it is complete and this is a very good programming practice. 

    It has even been mentioned that SQL Server may at some point require the use of command terminators, but we will just have to wait and see.


    http://jahaines.blogspot.com/
    Friday, August 20, 2010 3:03 PM
    Moderator
  • It has even been mentioned that SQL Server may at some point require the use of command terminators, but we will just have to wait and see.
    http://jahaines.blogspot.com/

    I agree. We're already seeing it with the use of CTEs although it would be cause a real stir if/when MS do so.
    every day is a school day
    Friday, August 20, 2010 3:15 PM
    Moderator
  • "GO" is not part of T-SQL, it is something related to the utilities. That is why you can not use "GO" inside your sp to indicate end of the batch. In T-SQL you use BEGIN ... END to delimit blocks.

    For example, this script will fail, even though the batch looks ok. Why?

    Because "GO" is not a T-SQL command.

    USE tempdb;
    GO
    DECLARE @sql nvarchar(4000);
    
    SET @sql = N'create table #T(c1 int)' + CHAR(10) + N'GO' + CHAR(10) + N'drop table #T' + CHAR(10) + N'GO';
    
    PRINT @sql;
    
    EXEC sp_executesql @sql;
    GO
    

    It is recognized by the utilities like SSMS / query window, but it is not sent to the engine as part of the batch.

    AMB

    Friday, August 20, 2010 3:33 PM
    Moderator
  • I would like to mark all of these as the answers!

    Many thanks - I have since built and tested my SP and it runs sweet. It's amazing how sometimes you can get involved heavily with T-SQL and forget some of the basics (well I can!).

    Many thanks

    Leeroyster

    Saturday, August 21, 2010 9:12 AM
  • I end each statement/block with a GO clause, to ensure it finishes running before the next peice of code.

    However, when I try to incorprate this into a stored procedure, I cannot use the GO command. If I remove all the GO lines, the sp will run

    The requirement for "GO" appears to be illogical with today's GUI interface, in fact, after many years of SQL-ing I still don't understand why is it required at some places not at others. In the following script it is required exactly at 2 places to make the script runnable repeatedly.

    -- T-SQL GO's required sometimes
    USE tempdb;
    SELECT * FROM AdventureWorks2008.Production.Product;
    SELECT * INTO Product FROM AdventureWorks2008.Production.Product;
    CREATE TABLE Alpha ( i int);
    CREATE TABLE Beta (i int);
    GO
    -- 'CREATE FUNCTION' must be the first statement in a query batch.
    CREATE FUNCTION fnTest (@ID int) RETURNS INT AS BEGIN RETURN (SELECT @ID+1); END;
    GO
    DROP TABLE tempdb.dbo.Product
    DROP TABLE Alpha
    DROP TABLE Beta
    DROP FUNCTION fnTest
    

    However, I know it exactly why you cannot use "GO" within the definition of an object like a stored procedure. Pre-1992 UNIX (ORACLE, SYBASE unixed based) scrolling only terminal window (no mouse either) was used to type in the commands to Sybase ("father" of SQL Server). When you  typed GO, you told the query processor client software: I am finished with typing single or multiple lines, send it for processing to the server.

    So if you include "GO" in the middle of a sproc definition, SQL Server query client software (SSMS query editor, SQLCMD) sends the preceding code as the definition (remainder as script) and SQL Server errors out on both parts or only on the remainder script.

    You still can experience the Bronze Age of RDBMS science with meaningful "GO"-s if you use SQLCMD (osql, isql) interactively, it is like a time machine, back 20-30 years! Don't use the mouse though, that is cheating!


    Kalman Toth
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016



    Sunday, August 22, 2010 4:12 PM
    Moderator
  • SQLUSA - I like it! Takes me back to my QBasic days:

     

    20: GO TO 10

     

    :)

    Sunday, August 22, 2010 4:37 PM
  • > So if you include "GO" in the middle of a sproc definition, SQL Server interpretes the preceding code as the definition and errors out on both parts or only on the remainder.

    No, this is incorrect. The query interface sees the GO, and sends the code between two GO as a batch to SQL Server. SQL Server never sees the GO.

    It is sometimes forgotten, or users are not aware of it, but SQL Server runs as server and you never communicate with directly. You always communicate through a client program.

    And while SQL Server Management Studio ships with SQL Server, it is not part of SQL Server as a program, but is a separate program which communicates with SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Sunday, August 22, 2010 6:16 PM
  • Good point Erland! I corrected my post.

    If you sit in front of SSMS 12 hours a day, it is easy to think that SSMS is the "face" of SQL Server. Nonetheless, it is only a client software, installed under "Client tools".

    If you trace server-client traffic with SQL Profiler, you can indeed see the the "GO"-s are not sent from SSMS Query Editor.

     


    Kalman Toth, SQL Server & Business Intelligence Training; Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    Sunday, August 22, 2010 7:16 PM
    Moderator
  • So would this be a correct conclusion then:

    When you are using DDL statements in a stored procedure (such as ALTER TABLE) that would, if executed from within a query window, require an intermediate GO command in order for subsequent statements to be able to rely on the completion of those DDL statements,
    this is the ONLY solution:

    Give each DDL statement its own stored procedure and EXEC those stored procedures from the calling stored procedure (nest them), for example:

    definition of stored procedure uspMassInsert:
        EXEC uspDropIndexOnTblA;
        INSERT INTO tblA SELECT ...;
        EXEC uspCreateIndexOnTblA;

    Or is there an easier way by calling a sqlcmd script file from the stored procedure?  (but then I assume it would be pretty hard to trap errors...)

    Thanks.



    • Edited by TechVsLife2 Wednesday, May 30, 2012 10:34 PM
    Wednesday, May 30, 2012 9:47 PM
  • You cannot call script files from stored procedures. As I said two years ago, SQL Server is a server application - and SQLCMD is a client.

    It's a little confusing because you seem to use DML to mean DDL. (DML is Data Manipulation Language, that is INSERT, UPDATE, DELETE and also SELECT according to some, while DDL is Data Definition Language, that is CREATE/ALTER TABLE etc.)

    Yes, if you want to interleave DDL and DML, you will need to make sure that the DML is not compiled until the DDL it depends on has been executed. Three solutions:

    1) Separate batches. (Don't forget to check @@trancount in the beginning of each batch.)=
    2) But most commands in EXEC().
    3) Have a program (in C# or whatever) to run the whole thing.

    My preference is for the latter.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 30, 2012 10:17 PM
  • Thank you, yes I meant to say DDL (edited).  ok I see by using EXEC I can keep it all in one sproc (with each DDL run by its own separate EXEC)--as you say EXEC is "akin to calling a nameless stored procedure created ad hoc."

    (I usually find C# to be a bit of overkill, at least for basic database plumbing.  I thought some people do run cmd line operations from sprocs using xp_cmdshell--but not elegant. perhaps wouldn't work for sqlcmd.)



    • Edited by TechVsLife2 Wednesday, May 30, 2012 11:22 PM
    Wednesday, May 30, 2012 10:53 PM