CODEWITHSUNDEEP
sql
central
central

Reputation: 25

A While loop inside a cursor's while loop - SQL

declare @i int
declare @skool float

declare schoolCursor cursor for  
select distinct choice from tempstuas

open schoolCursor
fetch next from schoolCursor into @skool
while @@fetch_status = 0
begin
        while @i < 20
            begin
                update top(1) tempstuas set cnt=@i where cnt = '' and cat = 1 and choice=@skool
                update top(1) tempstuas set cnt=@i where cnt = '' and cat = 2 and choice=@skool
                update top(1) tempstuas set cnt=@i where cnt = '' and cat=3 and choice=@skool

                set @i = @i + 1
            end
        fetch next from schoolCursor 
end
close schoolCursor
deallocate schoolCursor

This is basically going through a cursor that returns an individual location number. The location number is stored as a variable from the cursor which I need to use inside a while loop that iterates through a specific amount of times (20). What I am returned is just the cursor going on for the whole list of location numbers but does not iterate through the while loop with the update statements.

Upvotes: 0

Views: 14142

Answers (1)

HLGEM
HLGEM

Reputation: 96572

Cursors and while loops are generally the wrong way to solve a problem, I don't have time right now to figure out what exatly you are doing to suggest a set-based solution but seriously you need to start thinking in sets and stop thinking loops.

However your problem is that @i is null, null is not <20.

see this test of my theory

declare @i int

if @i<20 print'hi'
else print 'bye'

Upvotes: 2

Related Questions