-1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alison Niu
  • 64
  • 3
  • 2
    error message seems very clear. Also, the fact you want to do this in the first place leads me to suspect you have an XY problem. – Mitch Wheat May 04 '21 at 04:12
  • You cannot write SQL code using the same approach, patterns, and designs learned from other application languages. You will be more successful if you adapt to the design of SQL and its association with sets. Scalar UDFs are generally performance problems that come from a RBAR (not set-based) design. – SMor May 04 '21 at 12:01

1 Answers1

0

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.

Brucelin Michael
  • 475
  • 4
  • 10