CODEWITHSUNDEEP
sqlt-sqlsap-iq
theweeknd
theweeknd

Reputation: 287

How does the while loop work?

declare @t INT = 1 
declare @i INT = 1 

    while @t <= 5
BEGIN 
    while @i <= 40 
BEGIN 
    set @i = @i + 1
END

    set @t = @t + 1
    select @t
END

The result I get is @t=2. If I replace it with @i I get @i=41. Why does the @variable in the first while loop show 2, does it brake? Should it not show@t=6?

If I put the select @t at the end it will show 6, but what if you need every result from the iteration, you would not get the others results if you put the @t after the end, you would only get the last result. I would like the results 1,2,3,4,5,6 all go through @t ,and the end result should show only 6.

This is simplified example of a procedure that i have written, for every @t iteration the @i makes a full iteration, and then @t should go for the second iteration. @i works as planned, but the @t breaks after the first itteration.

(if @t = 1 , @i gets 40 rows from column 1 from some table, @t = 2 it gets 40 rows from column 2 from some table..ect) and writes it in @sql string, when she gets to 40 while loop breaks and goes into the @t loop, the @t should execute the @sql string(example it creates a view, so at the end i should have 6 views, but after the first execute the @t loop breaks.)

Upvotes: 0

Views: 139

Answers (2)

Amitesh
Amitesh

Reputation: 1517

You can store your result in a table variable.

DECLARE @t INT = 1 
DECLARE @i INT = 1 

DECLARE @Result table(Id int)

while @t <= 5
BEGIN 
  while @i <= 40 
  BEGIN 
    set @i = @i + 1
  END

  INSERT INTO @Result(Id) values(@t)
  set @t = @t + 1

END
select Id FROM @Result

Upvotes: 1

elvin
elvin

Reputation: 981

As I understand, you should have:

DECLARE @t INT = 1 
DECLARE @i INT = 1 

while @t <= 5
BEGIN 
  while @i <= 40 
  BEGIN 
    set @i = @i + 1
  END

  set @t = @t + 1
  --select @t   <-- this is breaking the loop before it ends
END
select @t

Upvotes: 0

Related Questions