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  lt  gt  -1  BEGIN IF    FETCH STATUS  lt  gt  -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  lt  gt  -1      BEGIN     IF    FETCH STATUS  lt  gt  -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     gt  1  OPEN OUTER CURSOR   FETCH NEXT FROM OUTER CURSOR INTO  CONTACT ID    TYPE   INDEX NO WHILE    FETCH STATUS  lt  gt  -1  BEGIN IF    FETCH STATUS  lt  gt  -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  lt  gt  -1          BEGIN         IF    FETCH STATUS  lt  gt  -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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt  -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

User · Answer

I had the same problem   what you have to do is declare the second cursor as  DECLARE  second cursor  Cursor LOCAL For  You see CURSOR LOCAL FOR  instead of  CURSOR FOR

User · Answer

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  gt  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  gt  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

User · Answer

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.

User · Answer

I had the same problem   what you have to do is declare the second cursor as  DECLARE  second cursor  Cursor LOCAL For  You see CURSOR LOCAL FOR  instead of  CURSOR FOR

User · Answer

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  gt  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  gt  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

User · Answer

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

User · Answer

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  lt  gt  -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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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.

User · Answer

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  gt  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  gt  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

User · Answer

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  lt  gt  -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

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt  -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

User · Answer

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  gt  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  gt  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

[sql] Cursor inside cursor

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql

Examples related to cursor