SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to do formaula calculation inside a string in
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ijmar86
Starting Member

India
8 Posts

Posted - 02/14/2013 :  06:03:10  Show Profile  Reply with Quote
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

3760 Posts

Posted - 02/14/2013 :  06:12:03  Show Profile  Reply with Quote
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

India
8 Posts

Posted - 02/14/2013 :  06:16:09  Show Profile  Reply with Quote
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 - 02/14/2013 :  06:55:50  Show Profile  Reply with Quote
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

India
8 Posts

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

rmg1
Constraint Violating Yak Guru

256 Posts

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

ijmar86
Starting Member

India
8 Posts

Posted - 02/14/2013 :  07:25:14  Show Profile  Reply with Quote
Yes it works,.
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
651 Posts

Posted - 02/15/2013 :  20:29:41  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000