Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Location
    sheffield, uk
    Posts
    14

    Unanswered: GO statement in SQL scripts

    I have inherited a large script which uses DateFrom and DateTo parameters in 4 separate places in the script. I would like to DECLARE these parameters and put them into 2 variables to use in the 4 instances (instead of having to alter the script every time I run it). My problem is that the GO statement ends a batch of commands and the script loses the value of my variables.

    Can anyone tell me exactly what the significance of the GO statement is in a SQL script, what it does and under what circumstances it is really necessary.

    Thanks for your help.

    itsmarkdavies@hotmail.com
    Mark Davies
    itsmarkdavies@hotmail.com

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    basically you need a go any time you issue a:

    CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, or CREATE VIEW statment. Also a table can not be altered and then referenced in the same batch.

    Check out Books On Line, on the index tab type in the keyword "GO". BOL has a very goo explination of the use of "GO" and batches in general.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    So Paul it sounds like 'GO' is like 'commit' when you create new objects in your script ?

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Not quite, transactions are used to group operations into a logical work unit which you commit or rollback based on some test. A "go" is syntacticaly required to execute some TSQL statments. Consider the following:

    Code:
    --------------------------------------------------------------------------------------------
    begin transaction
    create procedure test_getstuff as select db_name(), user_id()
    exec test_getstuff
    rollback transaction
    if object_id('test_getstuff') is not null
    print 'test_getstuff exists'
    else
    print 'test_getstuff does not exist'
    --------------------------------------------------------------------------------------------
    will generate an error, however

    Code:
    --------------------------------------------------------------------------------------------
    begin transaction
    go
    create procedure test_getstuff as select db_name(), user_id()
    exec test_getstuff
    rollback transaction
    if object_id('test_getstuff') is not null
    print 'test_getstuff exists'
    else
    print 'test_getstuff does not exist'
    go
    sp_helptext test_getstuff
    go
    --------------------------------------------------------------------------------------------
    will work because TSQL requires a create procedure to be the first command in a batch. Notice, however, that the stored procedure does a little more than expected. Lastly:

    Code:
    --------------------------------------------------------------------------------------------
    begin transaction
    go
    create procedure test_getstuff as select db_name(), user_id()
    go
    exec test_getstuff
    sp_helptext test_getstuff
    rollback transaction
    if object_id('test_getstuff') is not null
    print 'test_getstuff exists'
    else
    print 'test_getstuff does not exist'
    go
    --------------------------------------------------------------------------------------------
    The 2nd go is used to terminate the batch. Now the sp looks correct.

    Notice that in all of this I destroyed all my work by wrapping everything in a transaction. I could just ahve easily saved all my work by using a commit.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Jun 2002
    Location
    USA
    Posts
    21
    Hi,

    Use the "GO" statement if you want to end the SQL Statements and want SQL Server to execute till that.

    Mr. Paul and Mr.Youngman has explained in a nice way.

    You can add these below basic info also along with that.

    Basically SQL Server must understand where Your SQL script ends.
    So, Microsoft uses "GO" to terminate/end SQL Statements. Oracle uses ";" to terminate/end SQL Statements.

    As soon as SQL Server sees "Go" statement it understands that SQL Statement is ended and it can start executing the SQL Statements till that point.

    I can put in this way, like a man looking for pebbles. as soon as he finds a pebble, he picks and puts in a bag and looks nearby whether there are some more and keep going. Similarly SQL Server looks for "Go" and as soon as it sees "GO" it thinks, Ok I found a "go" and I have to end there and pick all the SQL Statements I come across and start executing.

    Thats it.

    Have Fun
    Varad01

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •