Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Eval arithmetic statement function in SQL Expand / Collapse
Author
Message
Posted Wednesday, December 15, 2010 12:00 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 14, 2013 3:48 PM
Points: 444, Visits: 162
Comments posted to this topic are about the item Eval arithmetic statement function in SQL
Post #1034912
Posted Wednesday, December 15, 2010 12:09 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, December 15, 2010 12:21 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 14, 2013 3:48 PM
Points: 444, Visits: 162
i get error 'RETURN statements in scalar valued functions must include an argument.'
when try to create to that function.
Post #1034922
Posted Wednesday, December 15, 2010 1:47 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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.

But the solutions remains intact. You can use EXEC('Select' + @strStatement) to evaluate an expression in your queries.
Post #1034948
Posted Wednesday, December 15, 2010 9:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:19 AM
Points: 168, Visits: 370
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
Posted Wednesday, March 9, 2011 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 2, 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 »

Add to briefcase

Permissions Expand / Collapse