I am attempting to convert VFP code into a stored procedure. Here is the pseudo-summary of how it works:
There are two tables:
Table 1 – Parts Stock Quantity and Prices. Example:

PART #     QTY      PRICE    PK VAL
001           2           $4          1
001           1           $2          2
001           4           $1          3

Table 2 – Parts Used and Prices. Example of structure:

PART #     QTY USED   PRICE
--------        -------------      -------

The procedure (in VFP and in SQL Stored Procedure) will receive a Quantity Used (e.g. 5)

I need to process this quantity against the Table 1 and reduce the stock quantities, and at the same time add a record in the Table 2 of used quantities and prices. For example, the Table 2 after processing Used quantity 5 would have the following records

PART #    QTY USED   PRICE
--------     -------------    -------
001         2                     $4
001        1                      $2
001         3                     $1

And the table 1 would have the following values:

PART #     QTY      PRICE    PK VAL
001           0           $4          1
001           0           $2          2
001           1           $1          3

In VFP this was done with a DO WHILE and other procedural code. What approach do I need for doing the same in SQL Server stored procedure?