>>>What's the correct syntax for calling this SP for each row to be returned?
>>>
>>>
>>>
>>>DECLARE @iInventoryOnHand INT
>>>
>>>
>>>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
>
>
>Ok, I've never done a UDF. Anything different than a sproc?
Look up Create Function in BOL. They are pretty straightforward. Normally you create a scalar function (true in your case) although you can also return a table.
The biggest difference between SP and function is that you have to declare the return type and actually RETURN it at the end.
____________________________________
Don't Tread on Me
Overthrow the federal government NOW!
____________________________________