title: Cursor inside cursor
body: <p>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. </p>
  
  <p>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.</p>
  
  <p>A part of the query looks like this:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>What can be the problem? Is @@FETCH_STATUS ambiguous or something?</p>
  
  <p>EDIT: everything looks fine if i don't use this code inside inner cursor:</p>
  
  <pre><code>UPDATE CONTACTS
  SET INDEX_NO = @COUNTER
  where current of INNER_CURSOR
  </code></pre>
  
  <p>EDIT: here is the big picture:</p>
  
  <pre><code>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
  </code></pre>