August 6, 2015 at 12:20 pm
Hi, is the "go" keyword necessary in a sproc? Won't the statements in a sproc get executed sequentially without the "go" keyword? Are there any scenarios where the "go" keyword is necessary? Or does it simply provide a visual separator? Does Microsoft or any other standards group recommend any type of standards/rules around using the "go" keyword in sprocs?
August 6, 2015 at 12:33 pm
You can't use go in a sproc. Anything after the first go won't be included in the sproc code.
It's not a SQL keyword as such, but a batch separator.
August 6, 2015 at 12:35 pm
GO is not a T-SQL keyword. It's an SSMS batch breaker. It delimits the batches sent to SQL Server. Since the CREATE procedure has to be in a batch by itself, if you have GO, it will be the end of the procedure
CREATE PROCEDURE Test
AS
SELECT * FROM SomeTable
GO
SELECT * FROM SomeOtherTable -- this is not part of the procedure definition
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2015 at 12:35 pm
GO is not a keyword. GO is a batch separator. You can actually define a different word instead of using GO as this is a setting for SSMS (aka, the client tool).
GO is only necessary when there are statements before the CREATE/ALTER PROCEDURE and when there are other statements after the end of the procedure that shouldn't be included.
August 6, 2015 at 12:48 pm
A neat feature of GO is that you can specify a number after it and SSMS will run the batch of statements that many times:
delete top (10) from mytable;
GO 25
One thing that drives me crazy is that SSMS doesn't STOP when a batch gets an error; it just goes on executing in sequence every batch in your script since you hit F5.
August 6, 2015 at 12:57 pm
Can you describe any specific scenarios where not having the "go" keyword will cause the SSMS batch to fail?
August 6, 2015 at 1:22 pm
All GO does is tell SSMS where to break the batches of commands that it sends to SQL Server. Nothing else.
Think of it the same as using two windows for commands, each being a batch.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2015 at 5:02 pm
There are plenty of situations where GO is required. Some CREATE/ALTER/DROP statements must be the first in the batch. And the word "EXEC" is optional for the first procedure call in a batch.
sp_who
exec sp_who
GO 5
August 7, 2015 at 4:24 am
sqlguy-736318 (8/6/2015)
Can you describe any specific scenarios where not having the "go" keyword will cause the SSMS batch to fail?
Let's say you have a script that creates two stored procedures. If you leave out the GO between the two, you'll get one stored procedure that, when called, attempts to create the second procedure (it'll work once, then fail).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 7, 2015 at 6:43 am
Grant Fritchey (8/7/2015)
If you leave out the GO between the two, you'll getone stored procedure that, when called, attempts to create the second procedure (it'll work once, then fail).an error saying that CREATE PROCEDURE must be the first statement in a batch.
However both of those scenarios are the same as putting the code before the GO into one SSMS window, the code after into a second and running them both. Or typing the first set of commands, running it, deleting the code, writing the second and running that
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply