top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to create Cursor inside cursor​ in sql server?

+1 vote
277 views
How to create Cursor inside cursor​ in sql server?
posted Jun 23, 2015 by Shivaranjini

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote
 
Best answer
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 Jun 24, 2015 by Manikandan J
...