>What's the correct syntax for calling this SP for each row to be returned?
>
>

>
>DECLARE @iInventoryOnHand INT
>
>-- I want to call this proc for each record
>EXEC spGetInventoryOnHand @sProdCode, @iInventoryOnHand OUTPUT
>
>SELECT i.sProduct_CD, <<call SP here>>
>	FROM Inventory i
>	JOIN Product p ON p.sProduct_CD = i.sProduct_CD
>	WHERE i.decInv_Physical_Qty <> 0 OR
>		  (@iInventoryOnHand > 0 and p.sCommodity_CD <> 'M')
>
>

It sounds like you should be using a function instead of an SP..

SELECT i.sProduct_CD, dbo.funcInvOnHand(p.sProdCode) as InventoryOnHand
FROM Inventory i
JOIN Product p ON p.sProduct_CD = i.sProduct_CD
WHERE i.decInv_Physical_Qty <> 0 OR
(@iInventoryOnHand > 0 and p.sCommodity_CD <> 'M')

and create the FuncInvOnHand to return the number