Eval arithmetic statement function in SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Bharat Panthee » Eval arithmetic statement function in SQL

Eval arithmetic statement function in SQL

Rate Topic

Display Mode

Topic Options

Author

Message

Bharat Panthee

Posted Wednesday, December 15, 2010 12:00 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 28, 2012 11:28 AM
Points: 444, Visits: 157

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

Post #1034912

Rajganesh Mountbatton

Posted Wednesday, December 15, 2010 12:09 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 21, 2011 2:12 AM
Points: 41, Visits: 32

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.

Post #1034916

Bharat Panthee

Posted Wednesday, December 15, 2010 12:21 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 28, 2012 11:28 AM
Points: 444, Visits: 157

i get error 'RETURN statements in scalar valued functions must include an argument.'
when try to create to that function.

Post #1034922

Rajganesh Mountbatton

Posted Wednesday, December 15, 2010 1:47 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 21, 2011 2:12 AM
Points: 41, Visits: 32

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. Sad

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

Post #1034948

vevoda.ulath

Posted Wednesday, December 15, 2010 9:35 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 20, 2013 2:33 AM
Points: 121, Visits: 288

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

Post #1035271

Jeferson Alexandre Borelli...

Posted Wednesday, March 09, 2011 9:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, April 02, 2011 2:23 PM
Points: 4, Visits: 28

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))')

Post #1075658

« Prev Topic | Next Topic »

Permissions