SQL Server Performance

Dealing with the Dodgy GO Command

Discussion in 'Performance-Related Article Discussions' started by shanetasker, May 11, 2006.

  1. shanetasker New Member

  2. mmarovic Active Member

    Interesting, I didn't know about behaviour described.
    Another issue I've had with Go command was related to assigning values to dynamic properties. When go command is used at the end of the query, no value will be returned. It caused bug in dts package I had hard time to solve.
  3. Adriaan New Member

    Probably the same issue as with USE: GO is not a control-of-flow keyword, but a batch delimiter.

    (1) Never try to use GO in a stored procedure - you don't need it anyway. It is valid in a script only, to separate different object definitions that must each be the first statement in a batch.

    (2) Variables in a script are immediately out of scope after the next GO (mirko's issue).

    One thing I missed in the article was this:

    If you use the /* and */ comment marks around a part of your script, and inbetween there is a line that contains only the word "GO", then this line will cause an error about a missing end comment mark. Change the line to "GO--" and the problem is solved - plus when you remove the comment marks, the script will still execute in a predictable way.
  4. biradar26 New Member

    Just to add to Adriaan comment on /* and */ and Go in one of the line. problem can be solved even by putting --GO.
  5. Adriaan New Member

    The advantage of putting -- after the GO is that when you remove the /* and */ marks, the script will execute with the GO in its proper place, and with the to-be-expected behaviour.
  6. ESquared New Member

    I think the reason you are confused is because you mistakenly think stored procedures require a BEGIN/END block. Consider the following:

    CREATE PROCEDURE Blah
    AS
    SET NOCOUNT ON
    CREATE TABLE #Blah (blahid int)
    INSERT #Blah SELECT 27 UNION SELECT 14 UNION SELECT 3
    SELECT * FROM #Blah
    DROP TABLE #Blah

    PRINT '--- That should do it ---'
    GOAre you surprised that the PRINT statement executes? You shouldn't be. It is perfectly clear that this stored procedure doesn't stop until the end of the batch, signfied by either a GO keyword or the true end of the submitted batch SQL.

    BEGIN/END blocks are at their core merely a way to group a series of statements so they function as a single statement. All procedural SQL expects a series of single statements, one followed by the other. In order to allow flow control using IF and WHILE, blocks had to be allowed. IF and WHILE expect a single statement after them. The next statement found is not part of the flow-of-control initiated by the IF or WHILE statement. Look:

    IF @myval = 2
    INSERT ErrorLog (ErrorMessage) VALUES ('Invalid myval of 2')
    PRINT 'Invalid myval of 2'
    ELSE
    EXEC DoSomething @myvalThis script will not run, because there are two statements between the IF and the ELSE keywords--the bolded PRINT statement breaks everything. We have to group it with the insert statement for it to be subject to the flow control initiated with IF:

    IF @myval = 2 BEGIN
    INSERT ErrorLog (ErrorMessage) VALUES ('Invalid myval of 2')
    PRINT 'Invalid myval of 2'
    END
    ELSE
    EXEC DoSomething @myvalI placed the BEGIN keyword on the same line as IF because the END logically matches up with the IF.

    The mere fact that one puts in BEGIN and END keywords around things has nothing to do with whether the next statement is executed or considered part of the Stored Procedure being created.

    For some distinction let's look at a function:

    CREATE FUNCTION Blah()
    RETURNS int
    AS
    BEGIN
    RETURN 0
    END
    PRINT 'It worked!'This returns the error Incorrect syntax near the keyword 'PRINT'.

    In SQL Server, scalar functions must have a BEGIN and END block. That makes sense: inline functions don't use a BEGIN/END block and in fact must be a single statement. So there's the unifying principle behind functions: at the top level, they must be only one statement long.

    Back to stored procedures. They're not named procedures for nothing! They consist of many statements, proceeding one to another to the next and so on. Thus a GO is required to know when they end. Let's go back to your MyBoss proc. We can add as many spurious begin/end blocks as we like, and it doesn't mean anything:
    Create proc MyBoss
    as
    select 'My boss is the best'
    begin
    begin
    begin
    begin
    select 'how fun'
    end
    end
    select 'all statements before a GO execute, no matter whether they''re in a block or not.'
    end
    end
    select 'This is a lie'
    begin
    select 'This stored procedure has five top-level statements. (Can you count them?) Some are begin/end blocks with more statements inside, and some are not.'
    end
    select 'A final statement'
    go
    So we see that the GO command isn't dodgy at all, if we gain a little understanding about how SQL Server expects stored procedures and functions to be laid out.

    Erik

Share This Page