Eval arithmetic statement function in SQL

  • Bharat Panthee

    Default port

    Points: 1448

    Comments posted to this topic are about the item Eval arithmetic statement function in SQL

  • Rajganesh Mountbatton

    SSC Enthusiast

    Points: 171

    Why complicating when a simple solution is available?

    Create Function EvalSQLStatement(@strStatement as varchar(1000))

    returns decimal(12,2)

    As

    Begin

    Return Exec('Select' + @strStatement)

    End

    Exec('Select' + @strStatement) will evaluate the expression and give its results.

  • Bharat Panthee

    Default port

    Points: 1448

    i get error 'RETURN statements in scalar valued functions must include an argument.'

    when try to create to that function.

  • Rajganesh Mountbatton

    SSC Enthusiast

    Points: 171

    Oh yes. There is a limitation in SQL Server. We can't use Exec inside UDF.

    EXEC('Select' + @strStatement) can't be used inside your function. 🙁

    But the solutions remains intact. You can use EXEC('Select' + @strStatement) to evaluate an expression in your queries.

  • vevoda.ulath

    Mr or Mrs. 500

    Points: 592

    Well, not really useful, since it doesn't support brackets.

    Draft send by Rajganesh on the other hand doesn't support decimals. (of course, I can force it)

    Don't have time right now to provide mine solution, but for this script to be useful it should works as follows:

    select dbo.EvalSQLStatement('(5 * 3 + 1) / 3')

    -- result should be 5.333333

  • Jeferson Alexandre Borelli Bernardo

    SSC Enthusiast

    Points: 138

    You dont need tocreate a function.

    A easier way using only EXEC :

    EXEC('SELECT CAST(

    (1.66 * 300 / 1170)

    + (1.75 * 220 / 1170)

    + (1.791 * 0 / 1170)

    + (1.6666 * 100 / 1170)

    + (1.96 * 350 / 1170)

    + (1.8716 * 100 / 1170)

    + (1.6666 * 0 / 1170)

    + (1.44 * 100 / 1170)

    AS DECIMAL(18,4))')

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply