Cursor inside cursor

Cursor inside cursor

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 by David B for Cursor inside cursor

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 = 0  BEGIN    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 ...  END  CLOSE @Outer  DEALLOCATE @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.

Answer by Mark Brittingham for 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 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.

Answer by cmsjr for Cursor inside cursor

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

declare @rowid int  declare @rowid2 int  declare @id int  declare @type varchar(10)  declare @rows int  declare @rows2 int  declare @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 sometable    select @rows = count(1) from @outer  while (@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 @outer  End  

Answer by Joel Coehoorn for Cursor inside cursor

This smells of something that should be done with a JOIN instead. Can you share the larger problem with us?

---

Hey, I should be able to get this down to a single statement, but I haven't had time to play with it further yet today and may not get to. In the mean-time, know that you should be able to edit the query for your inner cursor to create the row numbers as part of the query using the ROW_NUMBER() function. From there, you can fold the inner cursor into the outer by doing an INNER JOIN on it (you can join on a sub query). Finally, any SELECT statement can be converted to an UPDATE using this method:

UPDATE [YourTable/Alias]     SET [Column] = q.Value  FROM  (     ... complicate select query here ...  ) q  

Where [YourTable/Alias] is a table or alias used in the select query.

Answer by Orkun Balkanc? for Cursor inside cursor

I don't fully understand what was the problem with the "update current of cursor" but it is solved by using the fetch statement twice for the inner cursor:

FETCH NEXT FROM INNER_CURSOR    WHILE (@@FETCH_STATUS <> -1)  BEGIN    UPDATE CONTACTS  SET INDEX_NO = @COUNTER  WHERE CURRENT OF INNER_CURSOR    SET @COUNTER = @COUNTER + 1    FETCH NEXT FROM INNER_CURSOR  FETCH NEXT FROM INNER_CURSOR  END  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72