I have this function which is used on my view.
FUNCTION [dbo].[CalculateAmount] (
declare @computedValue decimal(24,4)
set @computedValue = case when
FROM dbo.[TABLE1] TABLE1
JOIN dbo.[TABLE2] TABLE2 ON TABLE2.ID = TABLE1.ID
WHERE TABLE1.ID = @ID // some conditions here //
@PRICE * CAST('1.0368' as decimal(24,4))
[dbo].[CalculateAmount](ID, PRICE) AS PRICE
If the only place you use your function is in the view, then why not just encapsulate the logic in the view:
ALTER VIEW dbo.YourView AS SELECT <columns>, CalculatedPrice = CASE WHEN t1.ID IS NULL THEN <tables>.Price ELSE 1.0368 * <tables>.Price END FROM <tables> OUTER APPLY ( SELECT TOP 1 t1.ID FROM dbo.Table1 AS t1 INNER JOIN dbo.Table2 AS t2 ON t2.ID = t1.ID WHERE t1.ID = <tables>.ID -- More Conditions ) AS t1 WHERE <predicates>;
The outer apply simply does the same check as your function to see if a record exists, then in the select statement, when a match is not found the price is multiplied by your constant, otherwise it is multiplied by 1.
You could create an inline table valued function for this. Unlike a scalar UDF this is not executed RBAR, but the query plan is expanded out into the outer query:
CREATE FUNCTION dbo.CalculateAmount (@ID INT, @Price DECIMAL(24, 4) RETURNS TABLE AS RETURN ( SELECT Price = CASE WHEN COUNT(*) = 0 THEN @Price ELSE @Price * 1.0368 END FROM dbo.Table1 AS t1 INNER JOIN dbo.Table2 AS t2 ON t2.ID = t1.ID WHERE t1.ID = @ID );
Then you would call it as:
SELECT <columns>, CalculatedPrice = ca.Price FROM <tables> OUTER APPLY dbo.CalculateAmount(ID, Price) AS ca WHERE <predicates>;