how evaluate an arithmetic expression within a SQL scalar function

Question

i am trying to execute this scalar function and i tried a lot of approaches to achieve this but i get stuck

Create FUNCTION CalculateElementFunc()
RETURNS int
AS
BEGIN
    DECLARE @ResultVar  numeric(18,6)
    DECLARE  @eq nvarchar(MAX)
    set @eq = '7.5/100*1258.236'    

    declare @expression nvarchar(max)
    set @expression = @eq

    declare @result int
    declare @SQLString nvarchar(max)
    Set @SQLString = N'Select @result = @expression'

    exec sp_executesql @SQLString, N'@expression nvarchar(100)', 
           @expression, 
           @result = @result output

    select @ResultVar = @result

    if( @ResultVar <> ROUND( @ResultVar, 2 ,1))
    set @ResultVar =  cast( ROUND( @ResultVar, 2 ,1) + .01 as numeric(18,2))

    RETURN @ResultVar
END

When i try to execute it

select dbo.CalculateElementFunc()

i get this error

Msg 557, Level 16, State 2, Line 1 Only functions and some extended stored procedures can be executed from within a function.

Please Advice

Answer 1

Limitations on SQL User Defined Functions:

1. Non-deterministic build in functions cannot be used in user defined functions. e.g. GETDATE() or RAND().
2. XML data type is not supported.
3. Dynamic SQL queries are not allowed.
4. User defined functions does not support any DML statements (INSERT, UPDATE, DELETE) unless it is performed on Table Variable.
5. We cannot make a call to the stored procedure. Only extended stored procedure can be called from function.
6. We cannot create Temporary tables inside UDFs.
7. It does not support Error Handling inside UDF. Although, we can handle errors (RAISEERROR, TRY-CATCH) for the statements which uses this function.

And it looks like you are using/calling a stored procedure inside your User Defined Function. It is not the expression that's bugging you, it's that stored procedure call.

Try to replace it with some logic to achieve your desired output.

Hope this is helpful. If it helps to solve your problem then don't forget to mark it as an answer.