Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Cursor for Update in Stored Procedure
Message
De
15/04/2003 11:56:05
 
 
À
Tous
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Stored procedures, Triggers, UDFs
Titre:
Cursor for Update in Stored Procedure
Divers
Thread ID:
00777828
Message ID:
00777828
Vues:
44
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
Fil
Voir

Click here to load this message in the networking platform