>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?

It is a tricky problem in SQL Server. There are several articles about this problem and I've written a complex procedure for our inventory.

Start your reading from this article:

https://ask.sqlservercentral.com/questions/1961/the-fifo-stock-inventory-sql-problem.html

BTW, if you want to read my article on this topic with ideas about our procedure, here it is:

http://social.technet.microsoft.com/wiki/contents/articles/18711.t-sql-fifo-inventory-problem-cost-of-goods-sold.aspx