Go go go?

  • anders-731262

    Ten Centuries

    Points: 1256

    Comments posted to this topic are about the item Go go go?

  • mmcginty

    SSC Veteran

    Points: 296

    "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]

  • anders-731262

    Ten Centuries

    Points: 1256

    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).

  • Ronald H

    SSCarpal Tunnel

    Points: 4023

    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

  • RBarryYoung

    SSC Guru

    Points: 143327

    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]

  • RBarryYoung

    SSC Guru

    Points: 143327

    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]

  • DaveD-128295

    Mr or Mrs. 500

    Points: 578

    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.

  • anders-731262

    Ten Centuries

    Points: 1256

    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?

  • DaveD-128295

    Mr or Mrs. 500

    Points: 578

    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.

  • webrunner

    SSC-Dedicated

    Points: 30306

    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

  • john.arnott

    SSChampion

    Points: 11882

    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

  • john.arnott

    SSChampion

    Points: 11882

    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.

  • RBarryYoung

    SSC Guru

    Points: 143327

    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]

  • Yelena Varshal

    SSC-Dedicated

    Points: 34249

    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

  • Anipaul

    SSC-Insane

    Points: 24681

    Wonderful question...

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply