Calculate Math using Function in SQL

Question

i tried 2 ways but both are not working inside a user defined function. THis is my 1st solution

-- SELECT dbo.ufn_CalculateMath('(9 + 4 + 2) / 3')
ALTER FUNCTION ufn_CalculateMath
    (
      @mathExpr NVARCHAR(MAX)
    )
RETURNS DECIMAL(18, 4)
    BEGIN
        DECLARE @retVal DECIMAL(18, 4)       

        DECLARE @arithExpr NVARCHAR(MAX) = @mathExpr
        DECLARE @arithFor NVARCHAR(MAX) = N'SELECT @tempResult = '
            + @arithExpr

        EXECUTE sp_executesql @arithFor, N'@tempResult varchar(30) OUTPUT',
            @tempResult = @retVal OUTPUT

        RETURN  @retVal
    END

and this is my second

-- SELECT  dbo.ufn_CalculateMath2('(9 + 4 + 2) / 3') 
ALTER FUNCTION ufn_CalculateMath2
    (
      @mathExpr NVARCHAR(MAX)
    )
RETURNS DECIMAL(18, 4)
    BEGIN
        DECLARE @arithTemp NVARCHAR(MAX) = @mathExpr
        DECLARE @sql1 VARCHAR(MAX) = 'SELECT ' + @arithTemp
        DECLARE @TABLE TABLE ( result DECIMAL(18, 4) )

        INSERT  INTO @TABLE
                EXECUTE ( @sql1
                       )

        DECLARE @x DECIMAL(18, 4)

        SELECT  @x = result
        FROM    @TABLE

        RETURN  @x
    END

The second solution wont compile since it is not allowed to add INSERT statement inside the UFN.

THe first solution will compile but there is an error upon running..

Is there another way of doing this?

Any help would be appreciated.

Thanks

The problem is probably that a function cannot be called inside a function. Here is someone with exactly the same problem: http://stackoverflow.com/questions/9850894/string-expression-to-be-evaluated-to-number It seems it cannot be done in SQL Server. — Thorsten Kettner, Sep 17 '14 at 11:20
You cannot execute a stored procedure (`sp_executesql`) or perform any DML actions (`INSERT`) from within a function. — Chris Pickford, Sep 17 '14 at 14:31
Out of the box question: Why are you doing math in SQL in the first place. I always try to avoid doing calculations or having business rules in SQL. — Alfons, Sep 17 '14 at 15:15

score 1 · Accepted Answer · answered Sep 17 '14 at 15:00

1

If your SQL Server version is high enough (SQL Server 2005 or later), you could do the evaluation in a .NET CLR function. This generally requires Visual Studio to compile the CLR function.

answered Sep 17 '14 at 15:00

Ross Presser

6,027
1
34
66

Calculate Math using Function in SQL

1 Answers1