Nuts - must be lack of lunch..

select sProduct_CD, QuantityOnHand
from
(SELECT i.sProduct_CD,dbo.udfGetInventoryOnHand(i.sProduct_CD)
FROM Inventory i
JOIN Product p ON p.sProduct_CD = i.sProduct_CD
WHERE i.decInv_Physical_Qty <> 0 OR p.sCommodity_CD <> 'M') as temp
where QuantityOnHand > 0


>how about..
>
>select sProduct_CD,dbo.udfGetInventoryOnHand(sProduct_CD) AS QuantityOnHand
>from
>(SELECT i.sProduct_CD
> FROM Inventory i
> JOIN Product p ON p.sProduct_CD = i.sProduct_CD
> WHERE i.decInv_Physical_Qty <> 0 OR p.sCommodity_CD <> 'M') as temp
>where QuantityOnHand > 0
>
>
>>Ok, here's what I have. The function returns 0 (for now).
>>
>>
>>

>>
>>SELECT i.sProduct_CD, dbo.udfGetInventoryOnHand(i.sProduct_CD) AS QuantityOnHand
>>	FROM Inventory i
>>	JOIN Product p ON p.sProduct_CD = i.sProduct_CD
>>	WHERE i.decInv_Physical_Qty <> 0 OR
>>		  (QuantityOnHand > 0 and p.sCommodity_CD <> 'M')
>>
>>

>>
>>I'm getting "Invalid column name 'QuantityOnHand'" on the last line. I'm guessing I'm referencing it wrong. Clue me in please.
>>
>>
>>
>>
>>
>>
>>>>>>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
>>>>
>>>>
>>>>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.