CODEWITHSUNDEEP
sqlsql-servert-sql
Vincent Dagpin
Vincent Dagpin

Reputation: 3611

Calculate Math using Function in SQL

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

Upvotes: 0

Views: 206

Answers (1)

Ross Presser
Ross Presser

Reputation: 6255

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.

Upvotes: 1

Related Questions