Cursor inside cursor

Question

Main problem is about changing the index of rows to 1,2,3.. where contact-id and type is the same. but all columns can contain exactly the same data because of some ex-employee messed up and update all rows by contact-id and type. somehow there are rows that aren't messed but index rows are same. It is total chaos.

I tried to use an inner cursor with the variables coming from the outer cursor. But It seems that its stuck in the inner cursor.

A part of the query looks like this:

Fetch NEXT FROM OUTER_CURSOR INTO @CONTACT_ID,  @TYPE While (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2)      DECLARE INNER_CURSOR Cursor      FOR      SELECT * FROM CONTACTS     where CONTACT_ID = @CONTACT_ID     and TYPE = @TYPE       Open INNER_CURSOR       Fetch NEXT FROM INNER_CURSOR      While (@@FETCH_STATUS <> -1)     BEGIN     IF (@@FETCH_STATUS <> -2) 

What can be the problem? Is @@FETCH_STATUS ambiguous or something?

EDIT: everything looks fine if i don't use this code inside inner cursor:

UPDATE CONTACTS SET INDEX_NO = @COUNTER where current of INNER_CURSOR 

EDIT: here is the big picture:

BEGIN TRAN  DECLARE @CONTACT_ID VARCHAR(15) DECLARE @TYPE VARCHAR(15) DECLARE @INDEX_NO  SMALLINT DECLARE @COUNTER SMALLINT DECLARE @FETCH_STATUS INT   DECLARE OUTER_CURSOR CURSOR   FOR   SELECT CONTACT_ID, TYPE, INDEX_NO FROM CONTACTS WHERE   CONTACT_ID IN (SELECT CONTACT_ID FROM dbo.CONTACTS WHERE CONTACT_ID IN(...) GROUP BY CONTACT_ID, TYPE, INDEX_NO HAVING COUNT(*) > 1  OPEN OUTER_CURSOR   FETCH NEXT FROM OUTER_CURSOR INTO @CONTACT_ID,  @TYPE, @INDEX_NO WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2)  SET @COUNTER = 1          DECLARE INNER_CURSOR CURSOR          FOR          SELECT * FROM CONTACTS         WHERE CONTACT_ID = @CONTACT_ID         AND TYPE = @TYPE          FOR UPDATE           OPEN INNER_CURSOR           FETCH NEXT FROM INNER_CURSOR           WHILE (@@FETCH_STATUS <> -1)         BEGIN         IF (@@FETCH_STATUS <> -2)          UPDATE CONTACTS         SET INDEX_NO = @COUNTER         WHERE CURRENT OF INNER_CURSOR          SET @COUNTER = @COUNTER + 1          FETCH NEXT FROM INNER_CURSOR          END         CLOSE INNER_CURSOR         DEALLOCATE INNER_CURSOR  FETCH NEXT FROM OUTER_CURSOR INTO @CONTACT_ID,  @TYPE, @INDEX_NO END CLOSE OUTER_CURSOR DEALLOCATE OUTER_CURSOR  COMMIT TRAN 

Answer 1

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 Cur1 FETCH NEXT FROM Cur1 INTO @ClientID; WHILE @@FETCH_STATUS = 0 BEGIN     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.