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

    Unanswered: SQL Server redirect problem

    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
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    "All problems are hard before they become easy" Albert Einstein


    Sounds to me like you shoudl split your program into a number of logical book selects based on the number of items required.
    E.g. if you have 5 items required, create 5 steps dynamically, complete that step, then move onto the remaining 4 steps etc , gradually stepping through the steps until the order is filled.

    That way each steap has rollback error correction and even if you need 5 copies of same book, each copy of the same book is treated as a logical step so the program doesnt care if it is the same as the previous book.

    That should work.

    More work for you but thats life.

    Cheers

    SG

  3. #3
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    10
    Hi SG,

    Thanks for your reply.
    This idea came to my mind before but i decided to keep it as my last option i am sure there must be some way to do this without breaking it in to number of logical records. 'Coz i alreadyw wrote this code in VB and i used "Rst.Requery" and it works fine. But in T-SQL i not sure which syntax to use. feel free to post more ideas.
    Thanks in advance
    Devinder

Posting Permissions

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