Cursor problem

[GAS]

I have tried to figure this out for a couple of days now and I'm not
making much headway. I have a cursor that I can't get to work
properly. There are two cursors in the stored procedure. The first
accesses data in the current database and works fine. The second
accesses data in another database on the same server. Whenever I
fetch rows into the second cursor (other database) the variables I
fetch into remain NULL.

I have included excerpts from the stored procedure. The whole
procedure is about 300 lines long so I only included a little snippet.

Thanks for any help you can provide.

DECLARE @v_salesOrderLine char(3),
@v_endItemNumber char(25),
@v_endItemRev char(3),
@v_dueDate datetime,
@v_orderQty decimal(15,5),
@v_endItemPrice decimal(17,5)

DECLARE c_salesOrderLineItems CURSOR
FOR SELECT fenumber,
fpartno,
fpartrev,
fduedate,
forderqty,
funetprice
FROM m2mdata05.dbo.sorels
WHERE fsono = @v_salesOrder

BEGIN

OPEN c_salesOrderLineItems

FETCH NEXT FROM c_salesOrderLineItems INTO @v_salesOrderLine,
@v_endItemNumber,
@v_endItemRev,
@v_dueDate,
@v_orderQty,
@v_endItemPrice
CLOSE c_salesOrderLineItems

END

[Erland Sommarskog]

GAS (gs*******@trinityforge.com) writes:

I have tried to figure this out for a couple of days now and I'm not
making much headway. I have a cursor that I can't get to work
properly. There are two cursors in the stored procedure. The first
accesses data in the current database and works fine. The second
accesses data in another database on the same server. Whenever I
fetch rows into the second cursor (other database) the variables I
fetch into remain NULL.

I have included excerpts from the stored procedure. The whole
procedure is about 300 lines long so I only included a little snippet.

Which unfortunately does not tell me much. I can't even tell whether
you actually need the cursor. (Often I see examples on the newsgroups
where people use cursors where you easily can find a set-based statement
that solves the problem with not only less lines of code, but also
a dramatic reduce in execution time.) So it's better to post all
the code.

The only I can say here, is that this is my recommendation on how to
write a cursor loop:

DECLARE cur INSENSITIVE CURSOR FOR
SELECT col1 ...
FROM ...
IF @@error <> 0 BEGIN DEALLOCATE cur RETURN END

OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @par1, ...
IF @@fetch_status <> 0
BREAK

-- Do stuff
END

DEALLOCATE cur

1) Use INSENSITIVE cursors. Default is keyset and they may work well,
or disastrously. INSENSIIVE copies the current rows into a worktable
and you are safe from surprises.

2) Check that cursors are not already open, because cursors are created
on process level by default.

3) Only one FETCH statement makes it easier to change if you add another
column to the SELECT statement. It's easy to forget that second
FETCH which may be 100 lines down.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp