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 (https://blog.sqlauthority.com) , Example BOL

,
Previous Post
SQL SERVER – Download Microsoft SQL Server 2005 System Views Map
Next Post
SQL SERVER – Fix : Error : Msg 6263, Level 16, State 1, Line 2 Enabling SQL Server 2005 for CLR Support

Related Posts

16 Comments. Leave new

  • 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?

    Reply
  • create table atable

    DECLARE @i int
    SET @i=1
    while @i<=100
    begin
    insert atable default values
    go
    set @i=@i+1
    end
    select * from atable

    Reply
  • 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.

    Reply
  • masterpiecebeta2
    March 4, 2010 12:58 pm

    simply understanable

    Reply
  • 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

    Reply
  • hi sir,
    its very usefull for me for your articles…..

    Reply
  • 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

    Reply
  • the above sp is not working properly… anyone help me…

    Reply
  • Excellent Site!!!!!!!!!!!!!!!!!!!

    Reply
  • Keep up the good work…Great site

    Reply
  • 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

    Reply
  • how many other statements we are using that are not actually TSQL statement?

    Reply
  • Jeremias Nuñez
    March 8, 2013 7:54 pm

    you can also use GO with an int parameter:

    INSERT INTO mytable DEFAULT VALUES
    GO 10

    that will insert 10 rows into ‘mytable’

    Reply
  • –(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

    Reply
    • Imran Mohammed
      May 23, 2013 8:43 pm

      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

      Reply
  • We can execute queries without using GO. Then
    What will happen if GO is not used in batch?

    Reply

Leave a Reply

Menu