Why sometimes sp_executesql can be used in function sometimes not?

Question

I have code in a SQL Server function:

CREATE FUNCTION mpc.fun1 (@exp NVARCHAR(30))--@exp like '2*5/4'
RETURNS DECIMAL(20, 10)      
AS
BEGIN
    DECLARE @result decimal(13,4)
    DECLARE @str nvarchar(30)=N'SELECT @aOUT = 1.0 *' + @exp

    EXECUTE sp_executesql  str, N'@aOUT DECIMAL(14,4) OUTPUT', @result OUTPUT;

    RETURN @result
END

I can run it by

DECLARE @a decimal(13,4);  
EXEC @a= mpc.fun1 '2*5/4'; 
PRINT @a

I can call it from a stored procedure or function for testing. But I can't run it from my working procedures.

I get the error:

Only functions and some extended stored procedures can be executed from within a function

I really don't know why?

Answer 1

In sql server functions, there are many restrictions, "EXECUTE sp_executesql" is one of them, you can consider using stored procedures to achieve, if you must use functions, you can use clr.