Errors with Cursor in Stored Procedure

Question

Errors with Cursor in Stored Procedure

M Jones

SSCommitted

Points: 1711
More actions
September 8, 2004 at 11:36 am

#162201

HELP

I am trying to test execution of newly coded Stored procedures. Of course I get an error on the first one!!! ARRRRGGGGG....
Here is how my cursor is defined:
DECLARE sort_cursor CURSOR
  FOR SELECT FULLORDERNO, WAREHOUSELOC
        FROM @mkfile1
        ORDER BY SUBSTRING(DIVISION,1,1),
                      FULLORDERNO,
                      ZONECOMBO,
                      WAREHOUSELOC
  FOR UPDATE OF ZONECOMBO
Syntax is correct but when executed I am getting the following error message and I don't know how to correct it. This should not be a READ ONLY cursor.
Server: Msg 16957, Level 16, State 4, Procedure mk_pld_sort1, Line 98
FOR UPDATE cannot be specified on a READ ONLY cursor.

Is there a setting somewhere I need to know about or something? I have several SP's with similar coding and I need to be able to use and updatable cursor.
Please HELP! Thanks!
~mj

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

bobsterboy SSCommitted Points: 1549 More actions · Answer 1

The problem that I see is that you don't have the field ZoneCombo listed in your cursor's select statement.

You use the OF keyword to limit the updating to specific fields. If you don't include the OF keyword, but just have FOR UPDATE, you can update any of the fields defined in the select statement for the cursor. If you don't select them, they don't exist, your error message probably should have been something more clear, like field doesn't exist in cursor, versus read only

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 2

before you start.....the best piece of advice you will get for free....

STAY away from CURSORS....poor performance...99% of CURSORS can be done in a SET-BASED manner.....for a fraction of the execution cost....

cursors are like filling a cup with sugar using tweezers....slow and painful

set-based is doing the same operation using a tablespoon.

search here for example of the above advice...

to solve your immediate problem....post more code....and chances are that it'll be re-written far better.

in the long run....you will learn far more + be more productive if you jump to SET-BASED processing asap.

lleston SSC-Addicted Points: 487 More actions · Answer 3

Andrew

I agree with you but in some cases the cursor is needed.

I try to make anything without a cursor but ... UNHAPPLY ... sometimes ... it is the only way

I HATE CURSORS ... Have you any tips to prevent use of then?

Michelle

Are you sure you need uses an update cursor? Isn’t there another way?

UPDATE CURSOS are the last option for me. Think again ... use a subquery or a pivot table … some times they help you to mount an SET-BASED structure, as efficientily as a cursor and the performance is too much better.

Regards

M Jones SSCommitted Points: 1711 More actions · Answer 4

Hi All,

Thanks for the input. I'm not totally sure this is the only way but it is definitely the best way I could come up with in the short time frame I've had to complete this project.

I will research other options but for now I have finally figured out what was wrong. It was the ORDER BY clause in my cursor definition which was causing my cursor to be changed to a READ ONLY cursor thus erroring when it found the FOR UPDATE OF clause. The fields in the ORDER BY are not referenced in any defined index.

Here's what the documentation says: "If a dynamic cursor is requested and the Transact-SQL statement contains an ORDER BY that does not match an index or subquery, the cursor is converted to a keyset-driven or static cursor."

I was able to remove the ORDER BY clause and still get the results I was looking for so for now all is good.

Thanks again to all who gave input. I appreciate it and will use it in the future!

Have a good day!

~mj

mcraigue SSC Veteran Points: 296 More actions · Answer 5

I'd strongly recommend the set-based approach mentioned above. Here's an example from BOL--incredibly fast compared to using a cursor:

This example modifies the ytd_sales column in the titles table to reflect the most recent sales recorded in the sales table.

UPDATE titles   SET ytd_sales = titles.ytd_sales + sales.qty      FROM titles, sales         WHERE titles.title_id = sales.title_id         AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

This example assumes that only one set of sales is recorded for a given title on a given date and that updates are current. If this is not the case (if more than one sale for a given title can be recorded on the same day), the example shown here does not work correctly. It executes without error, but each title is updated with only one sale, regardless of how many sales actually occurred on that day. This is because a single UPDATE statement never updates the same row twice.

In the situation in which more than one sale for a given title can occur on the same day, all the sales for each title must be aggregated together within the UPDATE statement, as shown in this example:

UPDATE titles   SET ytd_sales =       (SELECT SUM(qty)         FROM sales            WHERE sales.title_id = titles.title_id            AND sales.ord_date IN (SELECT MAX(ord_date) FROM sales))   FROM titles, sales

Hope that helps!

Mike