Cursor inside cursor

You have a variety of problems. First, why are you using your specific @@FETCH_STATUS values? It should just be @@FETCH_STATUS = 0.

Second, you are not selecting your inner Cursor into anything. And I cannot think of any circumstance where you would select all fields in this way - spell them out!

Here's a sample to go by. Folder has a primary key of "ClientID" that is also a foreign key for Attend. I'm just printing all of the Attend UIDs, broken down by Folder ClientID:

Declare @ClientID int;Declare @UID int;DECLARE Cur1 CURSOR FOR    SELECT ClientID From Folder;OPEN Cur1FETCH NEXT FROM Cur1 INTO @ClientID;WHILE @@FETCH_STATUS = 0BEGIN    PRINT 'Processing ClientID: ' + Cast(@ClientID as Varchar);    DECLARE Cur2 CURSOR FOR        SELECT UID FROM Attend Where ClientID=@ClientID;    OPEN Cur2;    FETCH NEXT FROM Cur2 INTO @UID;    WHILE @@FETCH_STATUS = 0    BEGIN        PRINT 'Found UID: ' + Cast(@UID as Varchar);        FETCH NEXT FROM Cur2 INTO @UID;    END;    CLOSE Cur2;    DEALLOCATE Cur2;    FETCH NEXT FROM Cur1 INTO @ClientID;END;PRINT 'DONE';CLOSE Cur1;DEALLOCATE Cur1;

Finally, are you SURE you want to be doing something like this in a stored procedure? It is very easy to abuse stored procedures and often reflects problems in characterizing your problem. The sample I gave, for example, could be far more easily accomplished using standard select calls.

You could also sidestep nested cursor issues, general cursor issues, and global variable issues by avoiding the cursors entirely.

declare @rowid intdeclare @rowid2 intdeclare @id intdeclare @type varchar(10)declare @rows intdeclare @rows2 intdeclare @outer table (rowid int identity(1,1), id int, type varchar(100))declare @inner table (rowid int  identity(1,1), clientid int, whatever int)insert into @outer (id, type) Select id, type from sometableselect @rows = count(1) from @outerwhile (@rows > 0)Begin    select top 1 @rowid = rowid, @id  = id, @type = type    from @outer    insert into @innner (clientid, whatever )     select clientid whatever from contacts where contactid = @id    select @rows2 = count(1) from @inner    while (@rows2 > 0)    Begin        select top 1 /* stuff you want into some variables */        /* Other statements you want to execute */        delete from @inner where rowid = @rowid2        select @rows2 = count(1) from @inner    End      delete from @outer where rowid = @rowid    select @rows = count(1) from @outerEnd

Do you do any more fetches? You should show those as well. You're only showing us half the code.

It should look like:

FETCH NEXT FROM @Outer INTO ...WHILE @@FETCH_STATUS = 0BEGIN  DECLARE @Inner...  OPEN @Inner  FETCH NEXT FROM @Inner INTO ...  WHILE @@FETCH_STATUS = 0  BEGIN  ...    FETCH NEXT FROM @Inner INTO ...  END  CLOSE @Inner  DEALLOCATE @Inner  FETCH NEXT FROM @Outer INTO ...ENDCLOSE @OuterDEALLOCATE @Outer

Also, make sure you do not name the cursors the same... and any code (check your triggers) that gets called does not use a cursor that is named the same. I've seen odd behavior from people using 'theCursor' in multiple layers of the stack.