T-SQL Nested Cursors Are Not Running Correctly

Question

Before I get any warlike cursor beating, let me say that I'm trying to use nested cursors to do something that I only need to do once, but if I run the online stored procedure once for each user and agency, I have done it a few hundred times.

I thought that the nested cursor in this case would save me some work, however, when I ran this script, it only goes through the outer cursor once, while the inner work is great for this run. In the test case, the external cursor set consists of two lines, and the internal one is about fifty. It goes through the first line of the outer cursor and all fifty inner ones, but then it is done.

As you can see, I save the result of the external selection ("@@ fetch_status"), so it does not interfere with the internal cursor.

I do not see what the problem is (obviously). Can anyone see what I can not?

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 appreciate all the answers.

In the end, I deleted the external cursor and just manually started the internal one. This saved me the trouble of manually entering 393 individual entries. I just needed to run the script three times.

Answer 2

The code looks as if it should work. Throw away the bill at the beginning:

 select count(*) from #users 

to double check the number of lines in #users ?

Answer 3

I'm not sure about troubleshooting a nested cursor, except there is a way to get rid of it and have only one cursor.

Make this select statement with cross-join:

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

Use this as a cursor definition. It must have any combination of user and agency / sub-agency.

Answer 4

I agree with Andomar, it should work. This test case goes through the outer loop 4 times, and the inner loop twice iterates. (Which corresponds to the number of rows in the corresponding 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