Need to use a nested cursor and getting "invalid object name"

Question

First, let me state right off that I'm well aware that cursors are generally evil and shouldn't be used - I'm all about using sets, but just couldn't come up with a set-based solution to this particular problem. If you tell me to go do some set-based operations, well, I'm all for it, if you can tell me how you'd code this particular problem.

Basically, I've got a number of stock items for which I need to make purchases. I want to make purchases based upon the cheapest available price, where I know the suppliers' prices and their stock levels. There's also a pack-size issue in here, wherein I want to buy by pack-size if possible.

I've already pulled a list of the things I need to purchase into #needorders, and suppliers' stock levels and prices into #orderedprices. Below I'm iterating through cursor CUR_NEEDED and creating a secondary cursor CUR_AVAILABLE:

    DECLARE CUR_NEEDED CURSOR LOCAL SCROLL_LOCKS
FOR
SELECT        GoodID
        , ConditionID
        , QuantityToShip
        , OrderStatusID
        , RetailerID
        , PackSize
FROM        #needorders
ORDER BY      GoodID
        , ConditionID
        , PurchaseDate DESC
FOR UPDATE

OPEN CUR_NEEDED
FETCH NEXT FROM CUR_NEEDED INTO @GoodID, @ConditionID, @QuantityToShip, @OrderStatusID, @RetailerID, @PackSize

WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE CUR_AVAILABLE CURSOR LOCAL SCROLL_LOCKS
        FOR
        SELECT        SupplierStocklistItemID
                , SupplierID
                , StockLevel
                , SupplierCurrencyID
                , CostPrice
        FROM        #orderedprices
        WHERE       #orderedprices.GoodID = @GoodID
        AND     #orderedprices.ConditionID = @ConditionID
        AND     #orderedprices.StockLevel > 0
        ORDER BY    #orderedprices.PriceRank
        FOR UPDATE

        OPEN CUR_AVAILABLE
        FETCH NEXT FROM CUR_AVAILABLE INTO @SupplierStocklistItemID, @SupplierID, @StockLevel, @SupplierCurrencyID, @CostPrice

        WHILE @@FETCH_STATUS = 0
            BEGIN
                /*
                Buy as many @PackSize as we need to cover how many we require, unless the supplier
                only has a certain number, in which case buy that number.
                E.g., need 14, pack size 5, 2 suppliers
                Supplier A has 11
                Supplier B has 40
                Buy 9 from Supplier A, with our remaining need being 3.
                Buy 5 from supplier B, with our remaining need being -2
                */
                --feed rows into #supplierpurchasesbase while @StockLevel > 0

                --Figure out how many we need to buy, based upon PackSize
                IF @QuantityToShip % @PackSize > 0
                    BEGIN
                        SET @Buy = @QuantityToShip - @QuantityToShip % @PackSize + @PackSize
                    END
                ELSE
                    BEGIN
                        SET @Buy = @QuantityToShip
                    END

                IF @StockLevel < @Buy
                    BEGIN
                        --PRINT 'Supplier only has ' + CAST(@StockLevel AS VARCHAR) + ' for us to buy.'
                        SET @Buy = @StockLevel
                    END

                INSERT INTO #supplierpurchasesbase (
                      GoodID
                    , ConditionID
                    , SupplierStocklistItemID
                    , Quantity
                    , SupplierID
                    , SupplierCurrencyID
                    , CostPrice
                    , RetailerID )
                SELECT    @GoodID
                    , @ConditionID
                    , @SupplierStocklistItemID
                    , @Buy
                    , @SupplierID
                    , @SupplierCurrencyID
                    , @CostPrice
                    , @RetailerID

                --update @QuantityToShip & the row in CUR_AVAILABLE
                IF @StockLevel <= @Buy
                    BEGIN
                        UPDATE  CUR_AVAILABLE
                        SET StockLevel = @StockLevel - @Buy
                        WHERE   CURRENT OF CUR_AVAILABLE

                        SET @QuantityToShip = 0
                    END
                ELSE
                    BEGIN
                        UPDATE  CUR_AVAILABLE
                        SET StockLevel = 0
                        WHERE   CURRENT OF CUR_AVAILABLE

                        SET @QuantityToShip = @QuantityToShip - @Buy
                    END

                --update the stocklevel so we don't see the thing again if we've used it up.

                IF @QuantityToShip = 0  --Don't need any more
                    BEGIN
                        UPDATE  CUR_NEEDED
                        SET OrderStatusID = @StatusPendingPO
                        WHERE   CURRENT OF CUR_NEEDED

                        BREAK
                    END
                ELSE    --Need more, move next, if we can
                    FETCH NEXT FROM CUR_AVAILABLE INTO @SupplierStocklistItemID, @SupplierID, @StockLevel, @SupplierCurrencyID, @CostPrice
            END
        CLOSE       CUR_AVAILABLE
        DEALLOCATE  CUR_AVAILABLE

        FETCH NEXT FROM CUR_NEEDED INTO @GoodID, @ConditionID, @QuantityToShip, @OrderStatusID, @RetailerID, @PackSize
    END
CLOSE       CUR_NEEDED
DEALLOCATE  CUR_NEEDED

The problem I'm running into is that I get I'm getting the error

Invalid object name 'CUR_AVAILABLE'.

when I'm attempting to update CURRENT OF CUR_AVAILABLE.

I've tried defining the CUR_AVAILABLE cursor as @CUR_AVAILABLE but get a different error. I've tried defining the CUR_AVAILABLE cursor outside of the WHILE loop of CUR_NEEDED, I've tried not closing / deallocating the cursor, etc. None of this seems to work.

Any ideas where I'm going wrong, here (other than not using sets, unless you've got a set-based solution)?

Andriy M · Accepted Answer · 2012-05-02 21:04:43Z

The following query uses a recursive CTE and, therefore, can't be considered a truly set-based solution. Nevertheless, I would still expect it to perform better than your two cursors (or to be worth trying, at the very least):

WITH buys (
  GoodID,
  ConditionID,
  SupplierStocklistItemID,
  Quantity,
  SupplierID,
  SupplierCurrencyID,
  CostPrice,
  RetailerID,
  PriceRank,
  RemainingNeed,
  PackSize
)
AS (
  SELECT
    GoodID,
    ConditionID,
    SupplierStocklistItemID = 0,
    Quantity                = 0,
    SupplierID              = 0,
    SupplierCurrencyID      = 0,
    CostPrice               = CAST(0.00 AS decimal(10,2)),
    RetailerID,
    PriceRank               = 0,
    RemainingNeed           = QuantityToShip,
    PackSize
  FROM #needorders
  UNION ALL
  SELECT
    p.GoodID,
    p.ConditionID,
    p.SupplierStockListItemID,
    Quantity = y.CurrentBuy,
    p.SupplierID,
    p.SupplierCurrencyID,
    p.CostPrice,
    b.RetailerID,
    p.PriceRank,
    RemainingNeed = b.RemainingNeed - y.CurrentBuy,
    b.PackSize
  FROM #orderedprices p
  INNER JOIN buys b ON p.GoodID = b.GoodID
    AND p.ConditionID = b.ConditionID
    AND p.PriceRank = b.PriceRank + 1
  CROSS APPLY (
    SELECT RemainingNeedAdjusted =
      (b.RemainingNeed + b.PackSize - 1) / b.PackSize * b.PackSize
  ) x
  CROSS APPLY (
    SELECT CurrentBuy = CASE
      WHEN x.RemainingNeedAdjusted > p.StockLevel
      THEN p.StockLevel
      ELSE x.RemainingNeedAdjusted
    END
  ) y
  WHERE p.StockLevel > 0
    AND b.RemainingNeed > 0
)
SELECT
  GoodID,
  ConditionID,
  SupplierStocklistItemID,
  Quantity,
  SupplierID,
  SupplierCurrencyID,
  CostPrice,
  RetailerID
FROM buys
WHERE PriceRank > 0
ORDER BY
  GoodID,
  ConditionID,
  PriceRank

Basically, the CTE forms the rows almost identical to those your query is inserting into #supplierpurchasesbase, except it additionally features auxiliary columns serving as kind of internal variables. (They are not pulled by the final SELECT, though.)

The anchor part forms a set of 0-quantity records based on the #needordered table, together with the initial values for the auxiliary columns. The recursive part contains all the logic: calculates the quantity to buy, updates the "remaining need" quantity for the next iteration, checks whether the next iteration is needed.

Certain assumptions have been made, and I hope you'll be able find your way around them if they do not match your real situation. For instance, quantities, pack sizes are assumed to be integer, and part of the logic relies on that, because it uses integral division. It is also assumed that PriceRank is a sequence of integers starting from 1, unique per (GoodID, ConditionID).

This script, as well as a minimal test setup, can be found, tested, modified, and tested on SQL Fiddle.

Thank you, Andriy! I'll give this a shot and if I can get it to duplicate the functionality of the two cursors then I'll flag yours as the answer - it's certainly preferable to dealing with cursors (I think I've had to write maybe 3 in the past 15 years, which is why I ran into such trouble getting these guys to work)!

David T. Macknet · Accepted Answer · 2012-05-02 10:53:42Z

0

The problem was twofold: The update syntax should not be:

UPDATE  CUR_AVAILABLE
SET StockLevel = @StockLevel - @Buy
WHERE   CURRENT OF CUR_AVAILABLE

Rather, the syntax should be:

UPDATE  #orderedprices
SET StockLevel = @StockLevel - @Buy
WHERE   CURRENT OF CUR_AVAILABLE

Also, in order to be updatable, the temp table needed to have a primary key:

ALTER TABLE #orderedprices ADD CONSTRAINT PRIMARY KEY CLUSTERED (RowCtr)

Lesson learned, I guess, but it certainly took me a fair bit of grief to find the solution!

answered May 2, 2012 at 10:53

David T. Macknet

3,1803 gold badges29 silver badges36 bronze badges

Collectives™ on Stack Overflow

Need to use a nested cursor and getting "invalid object name"

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related