Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Cursor for Update in Stored Procedure
Message
From
15/04/2003 11:56:05
 
 
To
All
General information
Forum:
Microsoft SQL Server
Category:
Stored procedures, Triggers, UDFs
Title:
Cursor for Update in Stored Procedure
Miscellaneous
Thread ID:
00777828
Message ID:
00777828
Views:
45
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
Map
View

Click here to load this message in the networking platform