SQL server 2008 Stored procedure syntax issue
<p>This is my first stored procedure and i would really appreciate some help crafting it :)</p>
<p>I have had a go but im getting the following error which appears wherever P.RL is. I tried renaming P.RL to Products.RL with the same result.</p>
<pre><code>Msg 4104, Level 16, State 1, Procedure UpdateInventoryForSCRAPorder, Line 19
The multi-part identifier &quot;Products.HD&quot; could not be bound.
Msg 4104, Level 16, State 1, Procedure UpdateInventoryForSCRAPorder, Line 19
The multi-part identifier &quot;Q.QuantityOrdered&quot; could not be bound.
</code></pre>
<p>Perhaps theres a more efficient way of doing what im trying, or maybe just a syntax tweak is needed.</p>
<p>The purpose of this Stored Proc is to update the inventory levels in a products table. There are two columns that hold the inventory figures. One column (RL) contains quantities of stock that have passed quality checks and are released (hence the RL) for ordering.</p>
<p>The other column (HD) is for stock that is held for various reasons, but it has been decided that they can be included in orders.</p>
<p>So what im trying to do here is ensure that the inventory is updated as soon as a product is ordered. The if statements are trying to cover all scenarios where for example some released product is ordered as well as some that is held.</p>
<p>So for example and order for 10 items: If there are 4 items in the RL column, add these first to the order and take the remainder from the held column.</p>
<p>All help is appreciated !</p>
<p>here is the stored proc</p>
<pre><code>CREATE PROCEDURE UpdateInventoryForSCRAPorder

@OrderID Int,
@LocalAmount Int

AS
BEGIN

    IF (Products.RL &gt; 0 and Products.HD &gt; 0) and (Q.QuantityOrdered &gt; Products.RL)
      Begin
    
        set @LocalAmount = Q.QuantityOrdered - RL
    
        UPDATE P
        Set RL = 0,
            HD = HD - @LocalAmount,
            P.OutstandingOrders = P.OutstandingOrders + Q.QuantityOrdered
        From
            Products P  join    
            (Select ProductID, sum(Quantity) as QuantityOrdered from OrderDetails where OrderID = @OrderId Group By ProductId)  Q 
            on P.ID = Q.ProductID
      END

    ELSE IF (P.RL &gt; 0 and P.HD &gt; 0) and (Q.QuantityOrdered &lt; P.RL)
        Begin
          UPDATE P
            Set RL = RL - Q.QuantityOrdered, 
            P.OutstandingOrders = P.OutstandingOrders + Q.QuantityOrdered
                From
                    Products P  join    
                    (Select ProductID, sum(Quantity) as QuantityOrdered from OrderDetails where OrderID = @OrderId Group By ProductId)  Q 
                    on P.ID = Q.ProductID
        END 
    ELSE IF (P.RL = 0 and P.HD &gt; 0) 
        Begin
          UPDATE P
            Set HD = HD - Q.QuantityOrdered, 
            P.OutstandingOrders = P.OutstandingOrders + Q.QuantityOrdered
                From
                    Products P  join    
                    (Select ProductID, sum(Quantity) as QuantityOrdered from OrderDetails where OrderID = @OrderId Group By ProductId)  Q 
                    on P.ID = Q.ProductID
        END 
    ELSE IF (P.RL &gt; 0 and P.HD = 0) 
        Begin
          UPDATE P
            Set RL = RL - Q.QuantityOrdered, 
            P.OutstandingOrders = P.OutstandingOrders + Q.QuantityOrdered
                From
                    Products P  join    
                    (Select ProductID, sum(Quantity) as QuantityOrdered from OrderDetails where OrderID = @OrderId Group By ProductId)  Q 
                    on P.ID = Q.ProductID
        END         

END
</code></pre>
<hr />
<h3>Solved</h3>
<p>After a good nights sleep and lots of caffeine, I tried a different approach and asked a different question.</p>
<p>Credit to Devart for the last piece of this puzzle. See <a href="https://stackoverflow.com/questions/34946367/sql-server-join-issue">Single update statement method</a></p>
<p>Apparently my requirement boils down to this:</p>
<pre><code>UPDATE Products
SET HD = HD + RL,
    RL = 0
FROM P
WHERE RL &lt; 0
    AND ID IN (
            SELECT ProductID
            FROM dbo.OrderDetails
            WHERE OrderID = @OrderId
        )
</code></pre>