469,572 Members | 1,281 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,572 developers. It's quick & easy.

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
Jul 20 '05 #1
1 1351
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
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by DarthMacgyver | last post: by
4 posts views Thread by Oliver Stratmann | last post: by
reply views Thread by Prashanth | last post: by
1 post views Thread by ilPostino | last post: by
5 posts views Thread by Vlad Simionescu | last post: by
2 posts views Thread by Craig | last post: by
6 posts views Thread by Tomek | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.