after loop wants to start for top with out closing cursor

Hi All,

I need your help urgently. I am using SQL Server. First i try to explain what i want to do. Imagine a warehouse having thousands of books or whatever. Sometimes multiple copies of same books can be on the same shelf but its also possible that 10 copies of a book are placed in 10 different shelf means one book per shelf. So i am writing program to pick location for orders received. My progarm works fantastic if a customer order single copy of any number of differnt books. But if a buyer order a 5 copies of the same book and if first location my progarm picked has only 1 or any number less than actual quantity ordered then my program fails to pick second or third location.
I am Using a cursor to fetch and process every single order and execute a stored procedures to pick location and update inventory. I want that after it has picked first location program should run last step without with same values in cursor but one updated value: (just want to redirect to last step) In VB we can use rst.Requery but what about SQL server

Following is a my code

CREATE PROCEDURE OPENCRSR2

--parametes needed to birng orders info in to program
@.var_orid varchar(25),
@.var_itemid varchar(25),
@.var_sku Varchar(25),
@.var_qtsold numeric(9)

AS

--variables to store fetched info from tables

Declare @.var_shelf numeric(9) ,
@.var_dateadded smalldatetime,
@.var_qtonhand numeric(9),
@.var_sporder varchar(25),
@.var_supplier varchar(25),
@.var_qtstore numeric(9)


DECLARE CrsrInside CURSOR FOR

SELECT Min(A.Shelf), A.DateAdded, Sum(A.QuantityOnHand) FROM UniversalBooks.dbo.tblInventory AS A
WHERE ((A.ISBN) = @.var_sku AND (A.QuantityOnHand>0)
AND (A.DateAdded=(SELECT Min(B.DateAdded)
From UniversalBooks.dbo.tblInventory as B
WHERE ((A.ISBN =B.ISBN) AND (B.QuantityOnHand>0)))))
GROUP BY A.DateAdded, A.QuantityOnHand

OPEN CrsrInside

FETCH NEXT FROM CrsrInside
INTO @.var_shelf, @.var_dateadded, @.var_qtonhand
--SELECT @.var_sku as ISBN, @.var_shelf as Shelf, @.var_dateadded as Dateadded
EXEC de_fetchstage2 @.var_sku, @.var_shelf, @.var_dateadded, @.var_qtonhand OUTPUT, @.var_sporder OUTPUT, @.var_supplier OUTPUT

--SELECT @.var_qtonhand as QtAvialable



IF @.var_qtsold > @.var_qtonhand
BEGIN
SET @.var_qtstore = @.var_qtsold - @.var_qtonhand
SET @.var_qtsold = @.var_qtonhand

--Following excute statement will insert picked shelf and other info into different table and subtarct quantity sold from quantity on hand but still i have few books to be picked for same order and @.var_qtstore has that number i want cursor should use same varibles but new value from @.var_qtsold which has the number of books to be picked(after execute Statement)


Exec de_insertstage3 @.var_sku, @.var_shelf, @.var_dateadded, @.var_qtonhand, @.var_sporder, @.var_supplier, @.var_orid, @.var_itemid, @.var_qtsold

SET @.var_qtsold = @.var_qtstore

--**** HERE I want to redirect my code to start from the current if statement instead of starting from next fetched cursor
END

ELSE
--REST OF THE CODE

--SELECT @.var_sku as ISBN, @.var_shelf as Shelf, @.var_dateadded as Dateadded, @.var_qtsold as Qtsold,
@.var_qtonhand as QuantityAvailable, @.var_sporder as OrderNumber, @.var_supplier as Supplier
END

CLOSE CrsrInside

DEALLOCATE CrsrInside
GO

Any help will be appericiated

Thanks in advance
DevWould a WHILE statement work for you?:

WHILE @.var_qtsold > @.var_qtonhand
BEGIN
SET @.var_qtstore = @.var_qtsold - @.var_qtonhand
SET @.var_qtsold = @.var_qtonhand
Exec de_insertstage3 @.var_sku, @.var_shelf, @.var_dateadded, @.var_qtonhand, @.var_sporder, @.var_supplier, @.var_orid, @.var_itemid, @.var_qtsold
SET @.var_qtsold = @.var_qtstore
END

blindman