2uzhan.com
Advertisement
Now Place:2uzhan.com » A T-SQL CURSOR issue, need help

A T-SQL CURSOR issue, need help

Microsoft SQL Server @ July 2, 2003   Views:0

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.

--------------Solutions-------------

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.

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

Tags:
© 2018 2uzhan.com Contact