-
Nested cursors with MS SQL 2000
I am posting a solution to nested cursors within MS SQL 2000. Currently, if you nest one cursor within a second cursor, when the inner cursor exits, it's @@FETCH_STATUS is set to -1 and then the outer cursor ends as well because @@FETCH_STATUS is a global variable, so they share the same @@FETCH_STATUS variable.
Solution:
DECLARE <your cursor> Cursor FOR
SELECT order_num
FROM orders
WHERE order_date > @currMonth
FOR Read Only
OPEN <your cursor>
WHILE ( 0 = 0 )
BEGIN
FETCH NEXT
FROM <your cursor>
INTO <your iterance variable>
--@fetchCheck is used to bypass global @@FETCH_STATUS
SET @fetchCheck = @@FETCH_STATUS
--Nested Cursor here, as a stored procedure.
EXEC spNestedCursor <your iterance variable>, @orderNum = @pullData OUTPUT
<your program code here>
--Cursor checks @fetchCheck instead of @@FETCH_STATUS
IF ( @fetchCheck <> 0 ) BREAK
END
CLOSE <your cursor>
DEALLOCATE <your cursor>
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|