title: Need to use a nested cursor and getting "invalid object name"
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>I've already pulled a list of the things I need to purchase into <code>#needorders</code>, and suppliers' stock levels and prices into <code>#orderedprices</code>.  Below I'm iterating through cursor <code>CUR_NEEDED</code> and creating a secondary cursor <code>CUR_AVAILABLE</code>:</p>
  
  <pre><code>    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 &gt; 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 &gt; 0
  
                  --Figure out how many we need to buy, based upon PackSize
                  IF @QuantityToShip % @PackSize &gt; 0
                      BEGIN
                          SET @Buy = @QuantityToShip - @QuantityToShip % @PackSize + @PackSize
                      END
                  ELSE
                      BEGIN
                          SET @Buy = @QuantityToShip
                      END
  
                  IF @StockLevel &lt; @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 &amp; the row in CUR_AVAILABLE
                  IF @StockLevel &lt;= @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
  </code></pre>
  
  <p>The problem I'm running into is that I get I'm getting the error </p>
  
  <blockquote>
    <p>Invalid object name 'CUR_AVAILABLE'.</p>
  </blockquote>
  
  <p>when I'm attempting to update <code>CURRENT OF CUR_AVAILABLE</code>.</p>
  
  <p>I've tried defining the <code>CUR_AVAILABLE</code> cursor as <code>@CUR_AVAILABLE</code> but get a different error.  I've tried defining the <code>CUR_AVAILABLE</code> cursor outside of the <code>WHILE</code> loop of <code>CUR_NEEDED</code>, I've tried not closing / deallocating the cursor, etc.  None of this seems to work.</p>
  
  <p>Any ideas where I'm going wrong, here (other than not using sets, unless you've got a set-based solution)?</p>