Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Stockholm, SWEDEN

    Unhappy Unanswered: A T-SQL CURSOR issue, need help

    Hi there,
    I have a very strange problem when using a t-sql cursor, appritiated if someone could explain why.

    When executing the SQL string to fill the cursor, everything works fine, nothin wrong so far

    But when executing the whole query (traversing the sql string result) I experience the following:

    First the code for the cursor
    USE pubs
    -- Declare the variables to store the values returned by FETCH.
    DECLARE @au_lname varchar(40), @au_fname varchar(20)
    DECLARE @iCounter smallint
    DECLARE authors_cursor CURSOR FOR
    SELECT au_lname, au_fname FROM authors
    WHERE au_lname LIKE "B%"
    ORDER BY au_lname, au_fname
    SET @iCounter  =  1
    OPEN authors_cursor
    -- Perform the first fetch and store the values in variables.
    -- Note: The variables are in the same order as the columns
    -- in the SELECT statement. 
    FETCH NEXT FROM authors_cursor
    INTO @au_lname, @au_fname
    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
       -- Concatenate and display the current values in the variables.
       PRINT "No: " + CAST(@iCounter AS varchar(3)) + " Author: " + @au_fname + " " +  @au_lname
      SET @iCounter = iCounter + 1
       -- This is executed as long as the previous fetch succeeds.
       FETCH NEXT FROM authors_cursor
       INTO @au_lname, @au_fname
    CLOSE authors_cursor
    DEALLOCATE authors_cursor
    As I said the SQL query to fill the cursor works fine, but the result from the whole query above is this!?

    No: 1 Author: Abraham Bennet
    No: 2 Author: Reginald Blotchet-Halls
    No: 3 Author: Abraham Bennet
    No: 4 Author: Reginald Blotchet-Halls
    No: 6 Author: Reginald Blotchet-Halls
    No: 7 Author: Abraham Bennet
    No: 8 Author: Reginald Blotchet-Halls
    No: 10 Author: Abraham Bennet
    No: 11 Author: Reginald Blotchet-Halls
    No: 12 Author: Abraham Bennet
    No: 13 Author: Reginald Blotchet-Halls
    It seams that the query omits som fetches, does anybody know why?

    In the BOL it says that WHILE loop only executes if the latest fetches succeeds, but why shouldnt it, the SQL query is ok!?

    appritiate some help,
    thanx in advance.

  2. #2
    Join Date
    Jun 2003
    I ran your code. Could not re-produce the error. Send me the data too. I assume you have fixed some sytax errors in your code.

  3. #3
    Join Date
    Jul 2003
    Stockholm, SWEDEN

    Solved it

    It was easier than I thougt, the error I mean.

    Yes, I to found some syntax error after submitting the post, but the error was that some fields in my rowset containded a null value, and when the code tried to print that value it failed, which meant that the specific row never showed up.

    I fixed by setting the vaule to 0

Posting Permissions

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