very strange problem with nested cursors

[demar]

I am using a sproc to query 2 tables and input data into a new table for use in a ColdFusion file.&nbsp;&nbsp;I use a cursor to select each row of a table (Issues) and another cursor to select all rows of another table (comments) that match the first cursor's item number, ordered by date.&nbsp;&nbsp;As long as the query for the first cursor specifies a discreet item in the Issues table with a WHERE clause, the second cursor finds the comments and outputs them as expected.&nbsp;&nbsp;If I don't use a where clause in the first cursor, I get nothing from the second cursor query.&nbsp;&nbsp;Any idea what is happening here?<br><br>First cursor:<br>DECLARE OuterCursor Cursor FOR<br>SELECT actionNumber, recommendation, priority, details, leadOrg, ownerLName, contributingOrg, currentDueDate, nextReviewDate, status FROM Issues&nbsp;&nbsp;/*have to include this for the second cursor to work*/ <br>WHERE actionNumber='4.3' (yes, it's a varchar field)<br>I fetch the outer cursor into variables, including @IactionNumber for use in the second cursor.<br><br>Second cursor:<br>DECLARE InnerCursor Cursor FOR<br>SELECT Comments.actionNumber, Comments.comment, Comments.date FROM Comments WHERE Comments.actionNumber =@IactionNumber ORDER BY date <br><br>AS long as I have the &quot;WHERE actionNumber='4.3&quot; or similar restrictive clause in the first cursor everything works fine: the second cursor finds all the comments and does with them what it's supposed to.&nbsp;&nbsp;If I leave out the WHERE clause or include something like &quot;WHERE @IactionNumber !='1000.0', I get more records returned by the first cursor, but nothing in the comments from the second cursor.<br><br>Why do I have to specify a WHERE clause in the first cursor?

 

[Guest_imported]

Nested Cursors are a no-no in SQL Server because there is only on @@fetch_status variable.

 

[MalcolmW]

My knowledge of this area is very weak, but it strikes me that you might be able to execute a stored proc from the outer curser which would contain the inner cursor, thus perhaps avoiding the single @@fetch_status variable issue. Or is fetch status (as it appears by the naming convention) a global variable, and thus this would be pointless. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[demar]

The&nbsp;&nbsp;first response sounds like it might be correct, but one of the SQL Server books in my library, Special Edition Using Microsoft SQL Server 7.0 (QUE, 1999), gives an example of nested cursors on page 630 that uses a &quot;WHILE @@fetch_status = 0&quot; for each cursor.&nbsp;&nbsp;Admittedly, I patterned my code after this example and it didn't work and I've seen mistakes in books, but those are usually typos, not entire examples showing functionality that doesn't exist in the product.&nbsp;&nbsp;Does anyone else have any information on whether you can or cannot use nested cursors in MS SQL Server?<br><br>BTW, I got my code to work by totally enclosing the inner cursor, from declaration to deallocation, within the outer cursor.&nbsp;&nbsp;This is very inefficient, but it was the only way I could get it to work.&nbsp;&nbsp;I'll give MalcolmW's suggestion, executing a stored proc from within the outer cursor,&nbsp;&nbsp;a try as soon as I get a little expiramentation time.&nbsp;&nbsp;It sounds like a good idea.

 

[IsraelZa]

2 to 3 yrs later...
Microsoft's documentation states that the @@FETCH_STATUS is a global variable, and therefore will be 0 (still records) in an inner cursor, even if only the outer one has more records, and vice-versa.

 

[IsraelZa]

To be more accurate: the FETCH_STATUS will reflect the last cursor that executed a FETCH statement state.

 

[royjimenez]

U shouldn't use cursor, it is only like last chance. Also u can do nested cursors ]; .
U could try changing your cursors with a temp table.

A question:

are u updating rows with the cursors?

Regards

 

[SQLSister]

Select Issues.actionNumber, recommendation, priority, details, leadOrg, ownerLName, contributingOrg, currentDueDate, nextReviewDate, status, Comments.comment, Comments.date FROM Issues Join Comments on
Issues.actionNumber = Comments.actionNumber
order by comments.date

You can add a where clause to this or not as you please. If you want to include records which have no comments, use a left join. This is not something that should be done with cursors as they are inefficient.