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

    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
    Code:
    USE pubs
    GO
    
    -- 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.
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
    
       -- 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
    END
    
    CLOSE authors_cursor
    DEALLOCATE authors_cursor
    GO
    As I said the SQL query to fill the cursor works fine, but the result from the whole query above is this!?

    Code:
    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
    Location
    Ohio
    Posts
    149
    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
    Location
    Stockholm, SWEDEN
    Posts
    3

    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
  •