Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    10

    Unanswered: 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
    Dev
    Thanks in advance
    Devinder

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Would 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •