Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ijmar86
Starting Member
8 Posts |
Posted - 2013-02-14 : 06:03:10
|
HI,I need to evaluate a formula in sql server (eg)((a+b)*c)/d, where this formula will be stored inside a string value likeDECLARE @strFormula VARCHAR(250)SET @strFormula = ((a+b)*c)/dAnd now a, b, c and d all will have some values like 15.25, 20, 22.50, 25 respectively.pls help me with how to evaluate this@mnyFinalOutput = the result of the executed formula (i.e) @strFormula with a,b,c,d values replaced. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-14 : 06:12:03
|
Does this give you the result you are looking for: DECLARE @strFormula VARCHAR(250)SET @strFormula = '((a+b)*c)/d';DECLARE @sql NVARCHAR(4000) = 'SELECT '+@strFormula;EXEC (@sql); If the tokens are columns in a table, you would need to do more work. But if that would do it for getting you the results in a select, it can then be modified to return the result in a variable using parameters and sp_executesql. |
|
|
ijmar86
Starting Member
8 Posts |
Posted - 2013-02-14 : 06:16:09
|
How to declare the value of a,b,c,d ?I have declared as below,DECLARE @a MONEY,@b MONEY,@c MONEY,@d MONEYSET @a= 10SET @b= 10SET @c= 10SET @d= 10DECLARE @strFormula VARCHAR(250)SET @strFormula = '((a+b)*c)/d';DECLARE @sql NVARCHAR(4000) = 'SELECT '+@strFormula;EXEC (@sql);Error Message : Msg 207, Level 16, State 1, Line 1Invalid column name 'a'.Msg 207, Level 16, State 1, Line 1Invalid column name 'b'.Msg 207, Level 16, State 1, Line 1Invalid column name 'c'.Msg 207, Level 16, State 1, Line 1Invalid column name 'd'. |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-02-14 : 06:55:50
|
This line:-SET @strFormula = '((a+b)*c)/d';would need to be :-SET @strFormula = '(('+@a+'+'+@b'+)*'+@c+')/'+@d;(I think, I haven't tested it) |
|
|
ijmar86
Starting Member
8 Posts |
Posted - 2013-02-14 : 07:01:52
|
Thank you so much, I have done it. :) |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2013-02-14 : 07:09:12
|
I assume it works then?And you're welcome. |
|
|
ijmar86
Starting Member
8 Posts |
Posted - 2013-02-14 : 07:25:14
|
Yes it works,. |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-02-15 : 20:29:41
|
It may work but, depending on the source(s) of information for the variables, you could be leaving yourself wide open for an SQL Injection attack. Without great and certain caution, you should avoid concatenation of user input into dynamic SQL.Look in Books Online for "sp_ExecuteSQL" for the proper way to do this type of thing.--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
|
|
|
|
|