| Author |
 Topic  |
|
|
ijmar86
Starting Member
India
4 Posts |
 Posted - 02/14/2013 : 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 like
DECLARE @strFormula VARCHAR(250)
SET @strFormula = ((a+b)*c)/d
And 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
Flowing Fount of Yak Knowledge
1497 Posts |
Posted - 02/14/2013 : 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
India
4 Posts |
Posted - 02/14/2013 : 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 MONEY
SET @a= 10
SET @b= 10
SET @c= 10
SET @d= 10
DECLARE @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 1
Invalid column name 'a'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'b'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'c'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'd'. |
|
|
|
rmg1
Posting Yak Master
245 Posts |
Posted - 02/14/2013 : 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
India
4 Posts |
Posted - 02/14/2013 : 07:01:52
|
| Thank you so much, I have done it. :) |
|
|
|
rmg1
Posting Yak Master
245 Posts |
Posted - 02/14/2013 : 07:09:12
|
I assume it works then?
And you're welcome. |
|
|
|
ijmar86
Starting Member
India
4 Posts |
Posted - 02/14/2013 : 07:25:14
|
| Yes it works,. |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 02/15/2013 : 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." |
|
|
| |
Topic |
|
|
|
How to do formaula calculation inside a string in
