THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Making the GO command live up to its fullest potential

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.

Published Saturday, June 28, 2008 12:32 PM by Jonathan Kehayias
Filed under:

Comments

 

Jonathan Kehayias said:

A good friend of mine an fellow MSDN Forum Moderator Aaron Alton recently wrote a blog post entitled

January 20, 2009 12:25 PM
 

Derek Wharton (Badpig) said:

That is interesting!

December 9, 2009 8:54 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement