SQL SERVER – Explanation SQL Command GO

GO is not a Transact-SQL statement; it is often used in T-SQL code. Go causes all statements from the beginning of the script or the last GO statement (whichever is closer) to be compiled into one execution plan and sent to the server independent of any other batches. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

GO Statement must be written in new line as it is not T-SQL command. T-SQL statement can not occupy the same line as GO. GO statement can contain comments.

Following is example for SQL SERVER 2005 for database Adventureworks.
USE AdventureWorks;
GO
DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'
GO ---- @MyMsg is not valid after this GO ends the batch.

—- Yields an error because @MyMsg not declared in this batch.
PRINT @MyMsg
GO
Reference : Pinal Dave (http://blog.SQLAuthority.com) , Example BOL

19 thoughts on “SQL SERVER – Explanation SQL Command GO

  1. Pingback: SQL SERVER - Execute Same Query and Statement Multiple Times Using Command GO Journey to SQL Authority with Pinal Dave

  2. Is there a way to determine which iteration of the batch is running when using GO [Count] to execute a batch multiple times? I would like to loop through a batch and pass the batch number to a stored procedure called within the batch. Is this possible?

    Like

  3. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  4. Are GOs implicit Commits? If I put a transaction around my script and hit an error, I thought it would rollback. But it doesn’t. If I remove all my GOs then any schema changes I make in my script aren’t accessible (meaning, say, if I created a table then I can’t insert into it) so I can’t run the sql I want to. I feel like I’m between a rock and hard place.

    Like

  5. Today I had a problem where we executed a sql script that included the following:

    1. Use PaceFSdB
    Go

    2. If the procedure exists already Drop it.
    Go

    3. Create The Procedure

    4. Grant Execute Permissions on the newly created Procedure.
    Go

    The problem:

    Number 4 above would never execute. The Proc did not have execute permissions granted to dbml.

    Notice on Number 3 above how we mistakenly omitted a GO statement after creating the procedure, which somehow was not executing Number 4 above correctly.

    Why is this?

    Thank you for all you informative posts.

    ElMatador

    Like

  6. alter PROCEDURE Base_Sp_InsertFieldDisplayOrder
    (
    @Template_No varchar(50),
    @Field_ID int,
    @DisplayOrder int,
    @OrgName varchar(50)
    )
    as
    declare @sql1 nvarchar(4000)
    set @sql1=”
    set @sql1=’
    aaa:
    if exists(select Field_Id from ‘+ @OrgName +’wdt_template_fields where template_no=’ +convert(varchar(50),@Template_No) + ‘ and DisplayOrder=’ + convert(varchar(50),@DisplayOrder) +’)
    begin
    declare @DisplayOrder int
    set @DisplayOrder=0;
    set @DisplayOrder = ‘ + convert(varchar(50),(@DisplayOrder + 1)) + ‘
    goto aaa
    end
    else
    begin
    update ‘+ @OrgName +’wdt_template_fields set DisplayOrder=’ +convert(varchar(50),@DisplayOrder)+’ where template_no=’ + convert(varchar(50),@Template_No) +’ and Field_id= ‘ +convert(varchar(50),@Field_ID) +';
    update ‘+ @OrgName +’wdt_template_properties set DisplayOrder=’ +convert(varchar(50),@DisplayOrder)+’ where template_no=’ + convert(varchar(50),@Template_No) +’ and Field_id= ‘ +convert(varchar(50),@Field_ID) +';
    update ‘+ @OrgName +’wdt_template_field_access_rights set DisplayOrder=’ +convert(varchar(50),@DisplayOrder)+’ where template_no=’ + convert(varchar(50),@Template_No) +’ and Field_id= ‘ +convert(varchar(50),@Field_ID) +';

    end’

    print @sql1
    –EXEC sp_executesql @sql1

    Like

  7. Hi Pinal,
    “go” is also very tricky!
    Check this:
    Domain\User doesn’t eixists on the Server, so I will catch the error and go on with the rest of the scirpt…
    If I let “go” in the TRY-Block I still get an error:

    Msg 102, Level 15, State 1, Line 6
    Incorrect syntax near ‘sysadmin’.
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘BEGIN’.

    But when I comment “go” out it works just fine!

    USE [master]
    go
    BEGIN TRY
    if not exists(select loginname from master.dbo.syslogins where name = ‘Domain\User’)
    begin
    CREATE LOGIN [Domain\User] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    end
    EXEC master..sp_addsrvrolemember @loginame = N’Domain\User’, @rolename = N’sysadmin’
    go
    END TRY
    BEGIN CATCH
    — select ‘CATCH’
    END CATCH
    –do next stuff

    Keep up your good work!
    Andreas

    Like

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #028 | SQL Server Journey with SQL Authority

  9. –(Procedure ‘abc’ is actually exist ) ,I want to alter the proc if it exist
    –so i write the below code ,But it fails
    –How to correct this and what is the problem with this .

    PRINT ”
    PRINT ‘Start of script (SP)!’

    IF EXISTS(SELECT 1 FROM sys.sysobjects WHERE name = ‘abc’ AND type = ‘P’)
    BEGIN
    SET ANSI_NULLS ON

    SET QUOTED_IDENTIFIER ON

    Alter procedure [dbo].[abc]
    as
    begin
    select * from tblParty
    end

    PRINT ”
    PRINT ‘End of script (SP)!’
    END

    Like

    • IF EXISTS(SELECT 1 FROM sys.sysobjects WHERE name = ‘abc’ AND type = ‘P’)
      BEGIN
      Exec(‘ SET ANSI_NULLS ON

      SET QUOTED_IDENTIFIER ON

      Alter procedure [dbo].[abc]
      as
      begin
      select * from tblParty
      end ‘)

      PRINT ”
      PRINT ‘End of script (SP)!’
      END

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s