Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Ryan, your problem is that @@FETCH_STATUS is global to all cursors in an connection. </p> <p>So the cursor within the trigger ends with an @@FETCH_STATUS of -1. When control returns to the code above, the last @@FETCH_STATUS was -1 so the cursor ends.</p> <p>That's explained in the documentation, which can be found on MSDN <a href="http://msdn.microsoft.com/en-us/library/ms187308(SQL.90).aspx" rel="nofollow noreferrer">here</a>.</p> <p>What you can do is use an local variable to store the @@FETCH_STATUS, and put that local variable in the loop. So you get something like this:</p> <pre><code>DECLARE @v1 int DECLARE @v2 int DECLARE @FetchStatus int DECLARE MyCursor CURSOR FAST_FORWARD FOR SELECT Col1, Col2 FROM table OPEN MyCursor FETCH NEXT FROM MyCursor INTO @v1, @v2 SET @FetchStatus = @@FETCH_STATUS WHILE(@FetchStatus=0) BEGIN IF(@v1&gt;10) BEGIN INSERT INTO table2(col1) VALUES (@v2) END FETCH NEXT FROM MyCursor INTO @v1, @v2 SET @FetchStatus = @@FETCH_STATUS END CLOSE MyCursor DEALLOCATE MyCursor </code></pre> <p>It's worth noting that this behaviour does not apply to nested cursors. I've made an quick example, which on SqlServer 2008 returns the expected result (50).</p> <pre><code>USE AdventureWorks GO DECLARE @LocationId smallint DECLARE @ProductId smallint DECLARE @Counter int SET @Counter=0 DECLARE MyFirstCursor CURSOR FOR SELECT TOP 10 LocationId FROM Production.Location OPEN MyFirstCursor FETCH NEXT FROM MyFirstCursor INTO @LocationId WHILE (@@FETCH_STATUS=0) BEGIN DECLARE MySecondCursor CURSOR FOR SELECT TOP 5 ProductID FROM Production.Product OPEN MySecondCursor FETCH NEXT FROM MySecondCursor INTO @ProductId WHILE(@@FETCH_STATUS=0) BEGIN SET @Counter=@Counter+1 FETCH NEXT FROM MySecondCursor INTO @ProductId END CLOSE MySecondCursor DEALLOCATE MySecondCursor FETCH NEXT FROM MyFirstCursor INTO @LocationId END CLOSE MyFirstCursor DEALLOCATE MyFirstCursor -- --Against the initial version of AdventureWorks, counter should be 50. -- IF(@Counter=50) PRINT 'All is good with the world' ELSE PRINT 'Something''s wrong with the world today' </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POWhy does my cursor stop in the middle of a loop?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRyan Barrett
    1. USRyan Barrett
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhy does my cursor stop in the middle of a loop?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. This table or related slice is empty.
 

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