General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
Title:
Cursor for Update in Stored Procedure
I've been trying to write an SP that has two cursors, the first (outer) I need to be able to do updates with, the second (inner) is read-only. I've DECLAREd the first cursor as follows:
DECLARE corrCursor CURSOR FOR
SELECT * from v_Open_Correspondence
FOR UPDATE Item_Age
Then I open the cursor and use FETCH NEXT to loop through the items in the View. For each item I open a read-only cursor for a subsidiary table, loop through the related items in the subsidiary table to compute the Item_Age value of the parent item. Once I'm done with the subsidiary table I close that cursor and try to do
UPDATE v_Open_Correspondence
SET Item_Age = @Age
WHERE CURRENT OF corrCursor
to update that column in the current row for corrCursor.
My problem, despite passing the syntax check in Enterprise Manager Stored Procedure Properties, I receive an error trying to execute the SP in Query Analyzer that says:
Server: Msg 16957, Level 16, State 4, Procedure AgeItems, Line 9
FOR UPDATE cannot be specified on a READ ONLY cursor.
Line 9 is the DECLARE for corrCursor.
Is there something about cursors in SP's that doesn't match the T-SQL definition of how cursors should work? This code was copied almost verbatim from several authoritative sources on T-SQL, but it doesn't work.
Any suggestions?
TIA
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only