Question about the GO statement

  • Hi,

    I know that using "GO 9" for example will execute a batch of T-SQL commands 9 times.

    E.g.


    /* this block should only get executed on the first iteration */
    DECLARE @greeting VARCHAR(100);
    SET @greeting = 'Hello';
    /* ==================== */

    /*  I want this block to repeat 9 times. */
    PRINT @greeting;

    SET @greeting = REVERSE(@greeting)

    GO 9

    Result:

    Beginning execution loop
    Hello
    Hello
    Hello
    Hello
    Hello
    Hello
    Hello
    Hello
    Hello
    Batch execution completed 9 times.

    I would like to get something like:

    Beginning execution loop
    Hello
    olleH
    Hello
    olleH
    Hello
    olleH
    Hello
    olleH
    Hello
    Batch execution completed 9 times.

    I'm wondering if there's a way to not execute that seed value in each subsequent run of the script. In other words, I want it to run the first time, then use the output of the query as the input to the next iteration (not including the seed "Hello") There may be a very obvious answer here that I'm overlooking, but any ideas? My real-life example involves a table variable that gets updated and eventually output, and it should be the input to the main query again, rather than starting at the top and having the seed information overwrite it all and therefore make it static. I was hoping to use the GO statement, but now I wonder if I would just use a loop instead. Is that the best way or is there another approach that is better these days?

    Again, feel free to shame me if there's something super obvious I'm missing here.

    Thanks,

    Mike

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • I think I see your mistake.  You're thinking of GO as being similar to, for example, a GOTO in BASIC, where it will jump up to a certain line.
    GO terminates the entire batch of the query, so in your example, everything from the top down.
    When you do GO # you execute the batch exactly the same way, every time.

    Plus, again, being a query, there's nothing to display when your query hits the "SET @greeting = REVERSE(@greeting)" portion, because the GO ends the query (before going all the way back to the top and starting fresh.)

    Maybe an example of what you're expecting would be a BASIC program, something like:
    5  Set X = 0
    10 Print "This is line 10"
    15  Set X= X + 1
    16  If X = 9 GOTO 30
    20 GOTO 10
    30 END
    Whereas with a SQL query GO #, it would be the equivalent of running the above code 9 times, the value of X gets reset every time (so your second run through, X is not equal to 9 at the start, it's equal to 0 again.)

    (I'm aware this is probably not the clearest explanation...)

  • jasona.work - Wednesday, December 6, 2017 11:41 AM

    Plus, again, being a query, there's nothing to display when your query hits the "SET @greeting = REVERSE(@greeting)" portion, because the GO ends the query (before going all the way back to the top and starting fresh.)

    Yes, that all makes sense, and thanks for that. I suppose then, what is it that I can use to distinguish that part of the code from the initialization of the variable such that I can have just the REVERSE portion run 9 times...? Again, maybe a loop? But then I'd have to check to see if it's the first run vs the 2-n run, correct?

    Thanks,
    Mike

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • Why don't you post something that's closer to your actual problem?  This particular problem can easily be solved with a set-based approach, but it sounds like your actual problem may be more difficult to do so.

    WITH Tally(n) AS
    (
        SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION)
        FROM
        (
            VALUES(1), (1), (1), (1), (1), (1), (1), (1), (1)
        ) v(n)
    )

    SELECT CASE WHEN n%2 = 1 THEN 'Hello' ELSE 'olleH' END
    FROM Tally

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • You *might* be able to do it with a cursor, but the question now would be "why are you trying to do what you're doing?"

    Although, you could mostly accomplish what you're trying to do by adding a second PRINT @greeting after the REVERSE, then doing GO 4

  • jasona.work - Wednesday, December 6, 2017 12:13 PM

    You *might* be able to do it with a cursor, but the question now would be "why are you trying to do what you're doing?"

    Although, you could mostly accomplish what you're trying to do by adding a second PRINT @greeting after the REVERSE, then doing GO 4

    A CURSOR requires something to iterate over, and there doesn't appear to be anything that qualifies here.  A WHILE loop might be an option, but I would explore a set-based option first.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Wednesday, December 6, 2017 12:21 PM

    jasona.work - Wednesday, December 6, 2017 12:13 PM

    You *might* be able to do it with a cursor, but the question now would be "why are you trying to do what you're doing?"

    Although, you could mostly accomplish what you're trying to do by adding a second PRINT @greeting after the REVERSE, then doing GO 4

    A CURSOR requires something to iterate over, and there doesn't appear to be anything that qualifies here.  A WHILE loop might be an option, but I would explore a set-based option first.

    Drew

    Ah, thanks, I wasn't sure on the cursor, it was the first "loop through" I could think of...


  • /* this block should only get executed on the first iteration */
    DECLARE @greeting VARCHAR(100);
    SET @greeting = 'Hello';
    /* ==================== */
    /* I want this block to repeat 9 times. */
    PRINT @greeting;
    PRINT REVERSE(@greeting);
    GO 9

    /*OR*/

    DECLARE @greeting VARCHAR(100);
    DECLARE @reverseGreeting VARCHAR(100);
    SET @greeting = 'Hello';
    SET @reverseGreeting = REVERSE(@greeting)
    /* ==================== */
    /* I want this block to repeat 9 times. */
    PRINT @greeting;
    PRINT @reverseGreeting;
    GO 9

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ScottPletcher - Wednesday, December 6, 2017 3:30 PM


    /* this block should only get executed on the first iteration */
    DECLARE @greeting VARCHAR(100);
    SET @greeting = 'Hello';
    /* ==================== */
    /* I want this block to repeat 9 times. */
    PRINT @greeting;
    PRINT REVERSE(@greeting);
    GO 9

    /*OR*/

    DECLARE @greeting VARCHAR(100);
    DECLARE @reverseGreeting VARCHAR(100);
    SET @greeting = 'Hello';
    SET @reverseGreeting = REVERSE(@greeting)
    /* ==================== */
    /* I want this block to repeat 9 times. */
    PRINT @greeting;
    PRINT @reverseGreeting;
    GO 9

    Either way, the entire set of code for each scenario, will run 9 times, as GO will execute the ENTIRE BATCH as many times as specified, with each execution being it's own separate batch.   If you want to see this in action, try a Profiler trace on your SPID with at least TSQL Statement Completed and TSQL Batch Completed being chosen as events.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson - Thursday, December 7, 2017 7:21 AM

    ScottPletcher - Wednesday, December 6, 2017 3:30 PM


    /* this block should only get executed on the first iteration */
    DECLARE @greeting VARCHAR(100);
    SET @greeting = 'Hello';
    /* ==================== */
    /* I want this block to repeat 9 times. */
    PRINT @greeting;
    PRINT REVERSE(@greeting);
    GO 9

    /*OR*/

    DECLARE @greeting VARCHAR(100);
    DECLARE @reverseGreeting VARCHAR(100);
    SET @greeting = 'Hello';
    SET @reverseGreeting = REVERSE(@greeting)
    /* ==================== */
    /* I want this block to repeat 9 times. */
    PRINT @greeting;
    PRINT @reverseGreeting;
    GO 9

    Either way, the entire set of code for each scenario, will run 9 times, as GO will execute the ENTIRE BATCH as many times as specified, with each execution being it's own separate batch.   If you want to see this in action, try a Profiler trace on your SPID with at least TSQL Statement Completed and TSQL Batch Completed being chosen as events.

    I know.  I guess otherwise to use the "GO nn" you'd need to store the value in a temp table and retrieve after an initial
    PRINT
    GO
    PRINT reverse
    PRINT forward
    GO 4

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • All,

    Thanks so much for the thoughtful replies. I know I didn't articulate my actual issue very well but it's partially because it's very complicated and I was hoping there would be a simple solution that I was missing. In any case, even though this isn't exactly what I had asked for in my original post, I *think* using a WHILE loop rather than trying to leverage GO # to iterate through my code may work. Something like:

    DECLARE @greeting VARCHAR(100);
    DECLARE @i INT;
    SET @greeting = 'Hello';
    /* set the number of iterations here instead of using GO 9 */
    SET @i = 9;

    WHILE @i > 0
    BEGIN
        PRINT @greeting;
        SET @greeting = REVERSE(@greeting);
        SET @i = @i - 1;
    END;

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • Mike Scalise - Thursday, December 7, 2017 7:48 AM

    All,

    Thanks so much for the thoughtful replies. I know I didn't articulate my actual issue very well but it's partially because it's very complicated and I was hoping there would be a simple solution that I was missing. In any case, even though this isn't exactly what I had asked for in my original post, I *think* using a WHILE loop rather than trying to leverage GO # to iterate through my code may work. Something like:

    DECLARE @greeting VARCHAR(100);
    DECLARE @i INT;
    SET @greeting = 'Hello';
    /* set the number of iterations here instead of using GO 9 */
    SET @i = 9;

    WHILE @i > 0
    BEGIN
        PRINT @greeting;
        SET @greeting = REVERSE(@greeting);
        SET @i = @i - 1;
    END;

    That will certainly accomplish the task.   The question remains, however, as to why it's necessary to PRINT the result instead of SELECT it.   If I recall correctly, somewhere in the chain of posts on this topic, someone used a CTE to drive a SELECT using a CROSS APPLY technique that could easily provide the same net result, and generally performs much better than WHILE loops do.   Perhaps the best way to ask that remaining question is what format does your output need to have?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Ok, I'm not sure if this helps or hurts, but here's a better representation of what I'm trying to do, which includes SELECT statements rather than PRINT statements and utilizes a CTE.

    DECLARE @TestTable TABLE (ID INT, n1 INT, n2 INT)
    INSERT INTO @TestTable VALUES
    (1, 5, 1),
    (2, 1, 5)

    ;WITH TestCTE AS (
    SELECT ID, CASE WHEN n1 = 5 THEN 1
                  WHEN n1 = 1 THEN 5
             END AS n1,
             CASE WHEN n2 = 1 THEN 5
                  WHEN n2 = 5 THEN 1
             END AS n2
    FROM @TestTable
    )
    SELECT *
    FROM TestCTE

    /*
    I'd like to replace the records in @TestTable with the results from TestCTE
    and have TestCTE run again with the new @TestTable values

    So to go through it...

    seed values to run through TestCTE
    ID    n1    n2
    1    5    1
    2    1    5

    results after running through TestCTE, which I'd like to become the new @TestTable and the input for the next iteration of TestCTE
    ID    n1    n2
    1    1    5
    2    5    1

    next set of results from TestCTE that'll become the new @TestTable
    ID    n1    n2
    1    5    1
    2    1    5

    etc., for a specified number of iterations

    */

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • Mike Scalise - Thursday, December 7, 2017 1:37 PM

    Ok, I'm not sure if this helps or hurts, but here's a better representation of what I'm trying to do, which includes SELECT statements rather than PRINT statements and utilizes a CTE.

    DECLARE @TestTable TABLE (ID INT, n1 INT, n2 INT)
    INSERT INTO @TestTable VALUES
    (1, 5, 1),
    (2, 1, 5)

    ;WITH TestCTE AS (
    SELECT ID, CASE WHEN n1 = 5 THEN 1
                  WHEN n1 = 1 THEN 5
             END AS n1,
             CASE WHEN n2 = 1 THEN 5
                  WHEN n2 = 5 THEN 1
             END AS n2
    FROM @TestTable
    )
    SELECT *
    FROM TestCTE

    /*
    I'd like to replace the records in @TestTable with the results from TestCTE
    and have TestCTE run again with the new @TestTable values

    So to go through it...

    seed values to run through TestCTE
    ID    n1    n2
    1    5    1
    2    1    5

    results after running through TestCTE, which I'd like to become the new @TestTable and the input for the next iteration of TestCTE
    ID    n1    n2
    1    1    5
    2    5    1

    next set of results from TestCTE that'll become the new @TestTable
    ID    n1    n2
    1    5    1
    2    1    5

    etc., for a specified number of iterations

    */

    Okay, but what are you going to use each iteration for?  In this specific case, you effectively have just two versions of a table. Not a lot of use that I can see from something this simple, but I suspect you're looking for a general methodology when a more specific methodology would probably perform better and be a much better solution in the long run.   Applying looping methods to large numbers of rows gets ugly in a hurry.   That's why I'm asking for the larger picture.   What you need to do with each iteration matters a great deal.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Each iteration is actually analyzing the values in the table and, depending on what the values are and where they are during that specific iteration will dictate exactly what it does. This is why it's not a one and done kind of thing. Each time it depends on what the values are. In this case, it just flips the 1 and 5 in each row depending on which value the field has during that iteration, but there's a plethora of things that it could do the numbers...and there's several more fields and rows....

    Mike Scalise, PMP
    https://www.michaelscalise.com

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

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