An SP cannot be called from a query but UDF can.

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

>
>DECLARE @iInventoryOnHand INT
>DECLARE @sProdCode VARCHAR(15)
>
>
>-- 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')
>
>