Dynamic SQL inside User defined functions

The below listed user defined function makes use of a Common Table Expression & a normal query which places it resultant in a local variable. My making use of both the features, we can replace the use of 'Execute immediate inside a user defined function'

Assume that we have a situation where the budget amount has to be summed up based on the branchId(s).

CREATE FUNCTION [dbo].[Fn_GetBudgetAmount](@IPNO VARCHAR(4000), @I_BRANCH VARCHAR(4000))
RETURNS FLOAT AS
BEGIN

Declare @V_RATIO_1NUMERIC(14,6), 
@V_RATIO_2NUMERIC(14,6),
@V_RATIO_3NUMERIC(14,6)

/*declare the variables to store the results*/
SET@V_RATIO_1 = 0.00
SET@V_RATIO_2 = 0.00
SET@V_RATIO_3 = 0.00

/* if the branch id is not null, then we go on with the manipulations using CTE */IF @I_BRANCH <> ''
BEGIN
BEGIN
With CTE_Ratio1(TotBudAlloc) As
(
SELECT IsNull(SUM(BUDGETALLOC),0) FROM <<TableName>> WHERE P_NO = @IPNO )
Select @V_RATIO_1 = TotBudAlloc From CTE_Ratio1
END

BEGIN
With CTE_Ratio2(TotBudAmt) As
(
SELECT IsNull(SUM(BUDGETAMT),0) FROM <<TableName>> WHERE P_NO = @IPNO 
)
Select @V_RATIO_2 = TotBchBudAmt From CTE_Ratio2

SET @V_RATIO_3 = (@V_RATIO_1 + @V_RATIO_2)
END
END
  
ELSE
/* Here if the branchid is null, then we move on to the calculations using queries returning values to local variables*/
IF @I_BRANCH IS NULL 
BEGIN    
    SELECT @V_RATIO_3 = IsNull(SUM(CHANELBUDALLOC),0) FROM CHANNEL_ALLOC_DTLS WHERE  
P_NO = @IPNO BRANCH_ID = @I_BRANCH 
   END
/*returning the resultant value*/RETURN (@V_RATIO_3)
END

The same in oracle would have been mapped up with 'Execute Immediate or dbms_sql.parse'. Since dynamic sql are not allowed inside a user defined function in t-sql we go on for CTEs and Queries to local variables