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.