Infinite SQL Loop Issue

Question

Super basic stuff here but i just can't seem to stop the loop repeating to infinity. I started by grabbing count values and storing them to the variable but when that didn't work i wanted to strip it back to the basics by just declaring the numbers to test them.

I've followed basic examples online and there doesn't seem to be anything extra in them that i don't have so apologies if this is level 0 stuff.

(I would expect the result of the select statement to be showing 11 times but it just goes on forever)

Declare @bid_loop int = 0
Declare @maxcount2 int = 10

While (@bid_loop <= @maxcount2)
BEGIN
    select * from @BranchIDList
END

Yeesh. Whatever happened to thinking about code while writing it, or at least trying to step through the code as if you were the computer when you have a problem. Here's a thought experiment: why *wouldn't* this loop forever? Why would anything ever change in your loop condition? — underscore_d, Apr 13 '18 at 15:49
"I would expect the result of the select statement to be showing 11 times"...why? You haven't done anything to make that happen. You're looping while the bid_loop variable is less than 10. Well, since you never change its value from 0, it always will be less than 10, forever. Step back and actually look at your code, this is really basic — ADyson, Apr 13 '18 at 15:51
@underscore_d OP named the variable loop. Computer should have known. — paparazzo, Apr 13 '18 at 15:51
A loop without a reason to exit. Now that's what I would call an old programmers pitfall. ;) And certainly not restricted to T-SQL. — LukStorms, Apr 13 '18 at 15:55
unless you increment the value of @bid_loop . it will always go in to loop forever — user9405863, Apr 13 '18 at 15:58
@paparazzo If AI was that smart there was no reason to code :p But some compilers or syntax checkers can already check for potential problems like this one and warn you. Just not sure something like that exists for T-SQL. — LukStorms, Apr 13 '18 at 16:02
@LukStorms I think you missed the sarcasm in paparazzo's post :-) — ADyson, Apr 13 '18 at 19:33
@ADyson Sir, you are correct. I had his message scanned for sarcasm, but the script kept looping. — LukStorms, Apr 13 '18 at 19:45

Lukasz Szozda · Accepted Answer · 2018-04-13T15:57:47.403

7

You need to increment @bid_loop:

Declare @bid_loop int = 0;
Declare @maxcount2 int = 10;

While (@bid_loop <= @maxcount2)
BEGIN
    select * from @BranchIDList;
    SET @bid_loop +=1;             -- here
END;

Basically it is equivalent of FOR LOOP (not available in T-SQL).

You could also rewrite it using GO:

count

Is a positive integer. The batch preceding GO will execute the specified number of times.

select * from @BranchIDList;
GO 11

edited Apr 13 '18 at 15:57

answered Apr 13 '18 at 15:48

Lukasz Szozda

162,964
23
234
275

1

Thanks Lad that makes perfect sense as with every other coding language but none of the examples online that i saw had that stage, should of tried it anyway but got myself in a twist. – baseballfury Apr 13 '18 at 15:50
1

Now I'm wondering what the point is of selecting the same dataset 11 times... I would say that the `go` thing is really just a shortcut for interactive use, though, and has little to no value in real-life code like SPs, functions, etc. (where it ends definitions, variable scopes, etc.) – underscore_d Apr 13 '18 at 16:29
Am storing a set of numbers in a temp table and looping through each one to query a different table in order to show the results in report format i.e. specifically targeting each field in a report and using declared variable to show the results of the loop. – baseballfury Apr 15 '18 at 17:17

Infinite SQL Loop Issue

1 Answers1