Nice question, fairly basic. I agree that the # trickery is a bit unnecessary.
Minor bitching about the title and explanation:
1) GO is not a T-SQL statement. It's a batch seperator that most clients recognise and honor. But if you write your own C# client and have it send "GO" to SQL Server, you'll get a syntax error message, as SQL Server does not recognise GO as a valid keyword.
2) A very minor distinction - because GO is processed by the client, GO 100 will not simply execute the batch 100 times; it will send it 100 times. The result will be the same, but you get more network traffic and more parse and compile time. If you want to save on those resources, write a single batch with the logic to execute the statement 100 times.
DECLARE @i int = 1;
WHILE @i <= 100
SET @i += 1;
when I did actually try to run the code it didn't seem to like the "100"... I got...
(1 row(s) affected)
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '100'.
The code is identical... I'm running this on a SQL Server 2000 box, but I don't think that matters...
The server does not matter, the client does. You need at least the SSMS version that ships with SQL Server 2005. It should work against any version server, as far as I know (but I never tried this).
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis