Inserting Rows and Updating a Table without using SQL Cursor

I'开发者_运维知识库m trying to create a MS SQL 2005 stored procedure to allocate stock from a warehouse for a particular component in an order. There are multiple batches of stock available and these have to be used in a particular order. I can loop through the available stock and allocate until the order is fulfilled but I'm trying to think more set based than sequential and avoid using a CURSOR.

Here is my query that gets the warehouse stock available for a particular order component;

SELECT 
        STOCK.ComponentId,
        STOCK.StockId,
        STOCK.ExpiryDate, 
        STOCK.BatchNo, 
        STOCK.StockQty, 
        ORDER_ITEMS.OrderQty        
    FROM 
        STOCK 
        JOIN ORDER_ITEMS ON ORDER_ITEMS.ComponentId = STOCK.ComponentId 
    WHERE 
        STOCK.WarehouseId = @WarehouseId 
        AND STOCK.StockQty > 0
        AND ORDER_ITEMS.OrderItemId = @OrderItemId

I've been putting this into a temp table or creating a CTE with the query and applying an ORDER BY clause to get the stock sorted as it needs to be used up. This gives me a result set like:

ComponentId | StockId | ExpiryDate | BatchNo  | StockQty | OrderQty
-------------------------------------------------------------------
359         | 3107    | 2013-10-01 | 132435-1 | 20       | 50
359         | 3215    | 2013-10-01 | 154558-1 | 100      | 50
359         | 3216    | 2014-01-01 | 154689-1 | 100      | 50

What I need to do is insert records into a STOCK_ALLOCATED table using as many of the batches of stock as necessary to fulfil the Order quantity. In the example above I would use all 20 from the first row and then need 30 from the second row.

This would need to insert two records into a STOCK_ALLOCATED table with the OrderItemId, StockId and the quantity (20 and 30) for the two used batches and also decrease the stock quantity in the STOCK table appropriately.

Assuming that the necessary transactions are in place to maintain the stock tables consistently is there a way to do the inserts and updates without using a CURSOR to loop through and track how much stock I've already allocated and how much I still require?

This is a test sample that will work for you:

SELECT  
    StockID,
    Quantity
FROM (
    SELECT  
        StockID,
        CASE 
            WHEN OrderQty - PreviousQty <= 0 THEN 0
        ELSE 
                CASE 
                    WHEN OrderQty - PreviousQty <= Stock 
                    THEN OrderQty - PreviousQty 
                ELSE
                    Stock 
                END
        END Quantity
    FROM (
        SELECT  
            a.StockID,
            a.Stock,
            a.OrderQty,
            a.OrderByField,
            ISNULL(SUM(b.Stock), 0) PreviousQty
        FROM    
            @Table a
            LEFT JOIN @Table b ON a.OrderByField > b.OrderByField
        GROUP BY a.StockID,
            a.Stock,
            a.OrderQty,
            a.OrderByField
        ) Orders
    ) Orders
WHERE   Quantity > 0

Basically, you will need to join the results you are getting on itself, so that you can sum the quantities of the previous reserved quantities, and subtract this from the StockQty to determine what you still need to fulfill.

In order to do that, though, you will need a unique OrderByField so that you can accurately filter out the previous StockQty values. If you don't have a stored value you can use, you can derive it using ROW_NUMBER() OVER. If that's the case, let me know if you need help with that.