Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2007
    Posts
    4

    Unanswered: Curious issue with cursor in a stored procedure.

    I have found an issue with using a cursor in a stored procedure, that I was hoping someone could help me to understand.

    I've written a stored procedure that uses a cursor to select a list of IDs from one table, and then updates the data associated with each ID in another table. The procedure should process a batch of a certain size with each pass.

    Initially, I declared the cursor thus:

    Code:
    declare borr_ids_to_proc cursor for
        select BORROWER_ID from address_tmp
        where ACTION not in ("NONE","DONE")
    However, I found that fetch statement returned @@sqlstatus = 2 well before it had processed the full batch, and with unprocessed data still remaining (the actual number it processed varied - in different passes it processed 23, 155 and 184 rows where the batch size was set to 1,000 rows, and c. 140,000 rows matching the where criteria). In order to trouble-shoot the issue, I modified the cursor definition so that I could identify the next row that should have been processed:

    Code:
    declare borr_ids_to_proc cursor for
        select BORROWER_ID from address_tmp
        where ACTION not in ("NONE","DONE")
        order by BORROWER_ID
    Since I have made this change, each batch has completed successfully (two of 1,000 rows and one of 5,000). Is this a known issue with cursors, a bug in Sybase, or something else? We're using ASE 12.5.4 running on Solaris

    Thanks,
    Rob

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    There are always exceptions but using a cursor at all will lead to very poor performance and buggy code in Sybase. Why not tell us what the updates are you're doing and we can see if we can write it without the cursor.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •