Databases
StackOverflow2013
Postsdbo
POCursor inside cursor
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCursor inside cursor
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJoel Coehoorn
    1. USOrkun Balkancı
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCursor inside cursor
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POCursor inside cursor
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POCursor inside cursor
      1. This table or related slice is empty.
      1. VTUpMod
    1. COOne more question: what version of sql server, because that will determine what we can use for creating the row numbers to replace your @counter in the inner cursor.
      singulars
      1. POCursor inside cursor
      1. USJoel Coehoorn
    2. COAre you aware of set based logic? Cursors should be used as a last resort...
      singulars
      1. POCursor inside cursor
      1. USMitch Wheat
    3. COSo can you be kind enough to offer an answer? if you need more info about the situation, i kind gladly provide it.
      singulars
      1. POCursor inside cursor
      1. USOrkun Balkancı
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload