Information générale
Forum:
Microsoft SQL Server
Catégorie:
Stored procedures, Triggers, UDFs
Titre:
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
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement