Cursor only returning one rowgreenspun.com : LUSENET : SQL Server Database Administration : One Thread
I have a simple cursor in a stored procedure. When executed from VB or from the SQL SERVER 6.5 Client Tools/Query Tool or TSQL Debugger it is exiting the while loop after the first row with a @@fetch_status = -1. When I run the cursor select on it's own in Query Tool it returns multiple rows like it should. @@error = 0. Have you ever had this happen to you? I know you can't run it, however here is the cursor and loop DECLARE cGetLoadOrders CURSOR FOR SELECT OrderID FROM tblLoadDetail WHERE LoadNumber = @intLoadNumber AND DeliveryDate = @dtDeliveryDate AND LoadStatus = 'O'
OPEN cGetLoadOrders FETCH NEXT FROM cGet INTO @varOrderID WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN -- processing here END /* IF (@@fetch_status <> -2) */ END /* (@@fetch_status <> -1) */ P.S. Thanks for the answer regarding rows being reordered Paul Tribe
-- Anonymous, February 24, 1999
I suspect the problem is in the section that you have simplified as "processing here". The "processing here" section must also fetch the next row from the cursor. For an example see the article titled, "cursors examples", example C. "Cursor with a Stored Procedure".
-- Anonymous, February 26, 1999