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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to do formaula calculation inside a string in

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 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
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.
Go to Top of Page

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 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'.
Go to Top of Page

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)
Go to Top of Page

ijmar86
Starting Member

8 Posts

Posted - 2013-02-14 : 07:01:52
Thank you so much, I have done it. :)
Go to Top of Page

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2013-02-14 : 07:09:12
I assume it works then?
And you're welcome.
Go to Top of Page

ijmar86
Starting Member

8 Posts

Posted - 2013-02-14 : 07:25:14
Yes it works,.
Go to Top of Page

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."
Go to Top of Page
   

- Advertisement -