Eval arithmetic statement function in SQL

Question

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

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

Rajganesh Mountbatton SSC Enthusiast Points: 171 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 SSChasing Mays Points: 622 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

SSC Guru

Points: 76202

May 17, 2016 at 12:48 pm

Thanks for the script.