December 6, 2017 at 11:31 am
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
December 6, 2017 at 11:41 am
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...)
December 6, 2017 at 11:55 am
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
December 6, 2017 at 12:11 pm
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
December 6, 2017 at 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
December 6, 2017 at 12:21 pm
jasona.work - Wednesday, December 6, 2017 12:13 PMYou *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
December 6, 2017 at 12:25 pm
drew.allen - Wednesday, December 6, 2017 12:21 PMjasona.work - Wednesday, December 6, 2017 12:13 PMYou *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...
December 6, 2017 at 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
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 7, 2017 at 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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 7, 2017 at 7:37 am
sgmunson - Thursday, December 7, 2017 7:21 AMScottPletcher - 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 9Either 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) A socialist is someone who will give you the shirt off *someone else's* back.
December 7, 2017 at 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;
Mike Scalise, PMP
https://www.michaelscalise.com
December 7, 2017 at 7:57 am
Mike Scalise - Thursday, December 7, 2017 7:48 AMAll,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)
December 7, 2017 at 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
*/
Mike Scalise, PMP
https://www.michaelscalise.com
December 7, 2017 at 1:52 pm
Mike Scalise - Thursday, December 7, 2017 1:37 PMOk, 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 valuesSo to go through it...
seed values to run through TestCTE
ID n1 n2
1 5 1
2 1 5results 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 1next set of results from TestCTE that'll become the new @TestTable
ID n1 n2
1 5 1
2 1 5etc., 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)
December 7, 2017 at 2:28 pm
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