August 24, 2008 at 10:25 pm
"G0 x" means run the above batch x times.
Wouldn't that be, "GO x" means return the value x, as the only [and unnamed] column, in the only row of the resultset returned? Or is there some way to "run the above batch" NULL times, that I don't know about?
An interesting question (even if slightly sick and twisted, from a naming convention perspective) but lost it on the back stretch.
-MM
[font="Comic Sans MS"]The Black Knight ALWAYS triumphs. Have at you![/font]
August 25, 2008 at 2:30 am
Try running something like
SELECT 1 AS A
GO 3
This will return three resultset with one row and one column each (containing a 1).
In the example however,
GO 3;
should have ran the procedure with 3 as an argument. For some reason this just gives "Fatal parsing error while parsing 'GO' in my ssms (a bit odd since running the proc by executing GO; works fine).
August 25, 2008 at 4:02 am
Such stored procedures are great for confusing anyone who needs to debug your code 🙂
Ronald
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
August 25, 2008 at 6:50 am
Good one. I got everything else right, except that I missed that "GO;" would correctly execute the "GO" sproc (since it usually returns the aforementioned error).
By the way, I think that the answer could better explain the SSMS will only intercept the "GO [n]" if it is the first thing (or only thing?) on the line.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2008 at 6:51 am
mmcginty (8/24/2008)
"G0 x" means run the above batch x times.
Wouldn't that be, "GO x" means return the value x, as the only [and unnamed] column, in the only row of the resultset returned? Or is there some way to "run the above batch" NULL times, that I don't know about?
No, the author has it correct. "x" defaults to one (1).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2008 at 7:41 am
Here's my interpretation of what happens. Note if you select and execute the lines by themselves you get different results than you do if you combine them into a batch.
1 CREATE PROC GO @GO int=NULL AS SELECT @GO -- Start a new batch
2 GO-- This GO terminates procedure definition batch
3 GO; -- Begin a new batch
4 GO 3 -- Is this a SQLCMD? Ignored as a batch terminator because of the 3? Start loop.
5 GO -- Terminate the batch
6 EXECUTE('GO 3') -- Begin a new batch
7 GO 3 -- Is this a SQLCMD? Ignored as a batch terminator because of the 3? Start loop.
8 GO -- Terminate the batch
9 DROP PROC GO -- Start a new batch to drop the proc
10 GO -- Terminate the batch dropping the procedure
So we have
Batch 1 lines 1 and 2 -- Define the proc
Batch 2 lines 3 - 5 -- Execute the proc with no parm 3 times
Batch 3 lines 6 - 8 -- Execute the proc with parm 3 times
Batch 4 lines 9 and 10 -- Drop the proc.
August 25, 2008 at 7:51 am
Actually the "GO"'s at 5 and 8 does nothing but terminates an empty batch (since it is already terminated by the "GO 3"=terminate and run thrice).
I beleive "GO 3" is something parsed and executed by SSMS alone and not SQLCMD?
August 25, 2008 at 8:24 am
Actually that's what puzzled me. BOL says that GO is not a T-Sql command but a command recognized by Sqlcmd, Osql, and the SSMS Code Editor. It also says that nothing can appear on the line with a GO except comments. Then in the doc for Sqlcmd it shows a GO with a Count. [:]go [count] It's iteresting that for the go command in Sqlcmd they show the colon as optional but not for the other commands.
August 25, 2008 at 9:29 am
That was a cool, tricky question.
I got this in Query Analyzer (SQL 2000):
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'GO'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'GO'.
but the code given did work in SSMS.
Thanks,
werbunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 25, 2008 at 10:27 am
I thank David Dresser for a good job parsing this out. I tried making it slightly more coder-friendly by renaming the proc "JO" and the variable "KO". I then found that all but the first delimiter "Go" statements didn't seem to be needed at all. CREATE PROC JO @KO int=NULL AS SELECT @KO
GO
JO;
GO 3
--GO
EXECUTE('JO 3')
GO 3
--GO
DROP PROC JO
--GO
August 25, 2008 at 10:41 am
Posts from Anders and webrunner point to how this script uses a new feature in SSMS not available in SQL 2000 QM. The Go 3syntax to run something multiple times is not available in QM.
This raises a red flag about testing. The script works fine in SSMS against a SQL 2000 database. This implies that even in a SQL2005 db, it may not work from a program or in a stored procedure.
I've already burned a half-hour on this and have too much to do today, so will leave the resolution of that question to others.
August 25, 2008 at 10:55 am
john.arnott (8/25/2008)
This raises a red flag about testing. The script works fine in SSMS against a SQL 2000 database. This implies that even in a SQL2005 db, it may not work from a program or in a stored procedure.
No it certainly will not. "GO" has always been a client interface feature. It does not work from a program or from a stored procedure and never has. Nor did the question imply that it would, it did specifically say "from SSMS".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2008 at 1:09 pm
GO should never be used as identifier.
http://msdn.microsoft.com/en-us/library/ms189822.aspx
Reserved Keywords (Transact-SQL)
This is not T-SQL keyword now but is on the list on ODBC keywords (second list in the referenced article) and on the third list of the future T-SQL keywords. This article says: "Although it is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, you can do this only by using delimited identifiers." About Future Keywords the article says: "The following keywords could be reserved in future releases of SQL Server as new features are implemented. Consider avoiding the use of these words as identifiers."
Regards,Yelena Varsha
August 26, 2008 at 6:35 am
Wonderful question...
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply