Nested T-SQL Cursors Not Executing Correctly

Question

Before I get any militant cursor-bashing, let me say that I'm trying to use nested cursors to do something I only need to do once, but if I ran the operative stored procedure once for each user and agency I have to do it a few hundred times. I thought a nested cursor in this case would save me some work, however, when I run this script it goes through the outer cursor only once, while the inner works just fine for that run. In the test case, the outer cursor set consists of two rows, and the inner one has about fifty. It goes through the first row of the outer cursor, and all fifty of the inner, but then it's done. As you can see, I am saving off the result of the outer fetch (the @@fetch_status) so it doesn't interfere with the inner cursor.

I can't see what the problem is (obviously). Can anyone see what I can't?

declare @fetch_user int
declare @fetch_agency int

declare user_cursor cursor for
select upn from #users

open user_cursor

fetch next from user_cursor into @upn

select @fetch_user = @@fetch_status

while @fetch_user = 0
begin

    declare agency_cursor cursor for
    select agency, subagency from agency_system where system_id = 1

    open agency_cursor

    fetch next from agency_cursor into @agency, @subagency
    select @fetch_agency = @@fetch_status

    while @fetch_agency = 0
    begin

        select @upn, @agency, @subagency

        EXEC AddUserToAgencyInRole
            @upn
            , @agency
            , @subagency
            , @system_id 
            , @role_id
            , @response output

        fetch next from agency_cursor into @agency, @subagency
        select @fetch_agency = @@fetch_status

    end

    close agency_cursor
    deallocate agency_cursor

    fetch next from user_cursor into @upn
    select @fetch_user = @@fetch_status

end

close user_cursor
deallocate user_cursor

Answer 1

I'm not sure about troubleshooting the nested cursor other than that there is a way to get rid of it and have only one cursor.

Make this select statement using a cross-join:

SELECT u.upn, a.agency, a.subagency
FROM #users u, agency_system a
WHERE a.system_id = 1

Use that as your cursor definition. It should have every combination of user and agency/subagency.

Answer 2

The code looks like it should work. Throw in a count at the start:

select count(*) from #users

to double check the number of rows in #users ?

Answer 3

I agree with Andomar it should work. This test case goes through the outer loop 4 times and the inner loop twice per iteration. (Which matches the number of rows in the respective tables)

set nocount on

DECLARE @upn INT, @agency INT, @subagency INT

CREATE TABLE #users (upn INT)

insert into #users select 1 union select 2 UNION select 3 UNION select 4

CREATE TABLE #agency_system(
agency INT, 
subagency INT,
system_id INT)

insert into #agency_system
select 1,1,1 UNION select 2,2,1

declare @fetch_user int
declare @fetch_agency int

declare user_cursor cursor for
select upn from #users

open user_cursor

fetch next from user_cursor into @upn
select @fetch_user = @@fetch_status

while @fetch_user = 0
begin
PRINT 'In Outer While Loop'

    declare agency_cursor cursor for
    select agency, subagency from #agency_system where system_id = 1

    open agency_cursor

    fetch next from agency_cursor into @agency, @subagency
    select @fetch_agency = @@fetch_status

    while @fetch_agency = 0
    begin
        PRINT 'In Inner While Loop'

       fetch next from agency_cursor into @agency, @subagency
        select @fetch_agency = @@fetch_status
    end

    close agency_cursor
    deallocate agency_cursor

    fetch next from user_cursor into @upn
    select @fetch_user = @@fetch_status

end

close user_cursor
deallocate user_cursor

drop TABLE #users
drop TABLE #agency_system

Answer 4

I appreciate all the responses.

I ended up eliminating the outer cursor and just manually running the inner one. This saved me from manually entering 393 separate entries. I just had to run the script three times.