Today I was trying to explain why the GO command is required between the statements of a CREATE PROCEDURE and its code block, and the GRANT EXECUTE statements to grant the rights to the Web Application Account. What was happening was the code was being checked in like the following:
CREATE PROCEDURE dbo.TestProc (@Variable1 VARCHAR(10))
AS
SELECT @Variable1
GRANT EXECUTE ON TestProc TO WEB_APP_USER This causes the GRANT EXECUTE to be a part of the procedure code. This has nothing to do with this post though. So I will get back to the point. In looking up the GO command in the BOL, I noticed that it is defined as:
Syntax :
GO [count]
Arguments :
countIs a positive integer. The batch preceding GO executes the specified number of times.
This allows you to specify code like:
DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'
PRINT @MyMsg
GO 5
The output of the above is:
Beginning execution loop
Hello, World.
Hello, World.
Hello, World.
Hello, World.
Hello, World.
Batch execution completed 5 times.
This has a number of great uses in testing code, or building tables with data. I did a search for GO multiple batches SQL Server on Google, and found some other articles/posts on this information as well. I found this very interesting, and will be using it when I build examples for the forums.