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
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?
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
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.
simply understanable
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
hi sir,
its very usefull for me for your articles…..
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
the above sp is not working properly… anyone help me…
Excellent Site!!!!!!!!!!!!!!!!!!!
Keep up the good work…Great site
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
how many other statements we are using that are not actually TSQL statement?
you can also use GO with an int parameter:
INSERT INTO mytable DEFAULT VALUES
GO 10
that will insert 10 rows into ‘mytable’
–(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
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
We can execute queries without using GO. Then
What will happen if GO is not used in batch?