.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal

Home >> Code Snippets >> SQL Query >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

How to evaluate a Formula using SQL SERVER

Posted By:Kemal AL GAZZAH       Posted Date: March 23, 2012    Points: 40    Category: SQL Query    URL: http://www.dotnetspark.com  

Explain a way to evaluate a Formula entered by user, using SQL SERVER

I was asked to find a way to evaluate the value given by a Formula which is filled by the user.
The Formula contains mathematical operations (addition, multiplication, division and substraction)
The parameters used to calculate the formula are stored in the sql server DATA BASE.

The solution I found by myself was as follows:

Suppose I have n parameters used to calculate the formula, each of these parameters is stored in one row in one data table.
- The data table containg the n rows to use in teh formula is called tab_value
- I have to store the n values found in n rows (in tab_values) in one single row in one new Table, using sql cursor,
- for that I create a new table called tab_formula
- In the cursor, I will add a new column for each value, the column name will be Id1,Id2,Id3 etc.
- Then I construct a sql script containing the formula to evalaute the formula

Here after the complete script, I hope you find it usefull, you are welcome to ask me about it.

if exists(select 1 from sysobjects where name='usp_evaluate_formula_KAMEL' and xtype='p') drop  proc  usp_evaluate_formula_KAMEL
create proc usp_evaluate_formula_KAMEL(@formula as nvarchar(100))
as begin

if exists(select 1 from sysobjects where name='tab_values') drop table tab_values
if exists(select 1 from sysobjects where name='tab_formula') drop table tab_formula

--declare @formula as nvarchar(100)
--set @formula='10*id1+id2*id3/id4'

if not exists(select 1 from sysobjects where name='tab_values') create table tab_values (id int, val  decimal(10,2))

if not exists(select 1 from tab_values where id=1) insert into tab_values(id,val) values(1,10)
if not exists(select 1 from tab_values where id=2) insert into tab_values(id,val) values(2,15)
if not exists(select 1 from tab_values where id=3) insert into tab_values(id,val) values(3,25)
if not exists(select 1 from tab_values where id=4) insert into tab_values(id,val) values(4,30)

declare @id as int
declare @val as decimal(10,2)

if not exists(select 1 from sysobjects where name ='tab_formula') create table tab_formula(id int identity(1,1), formula nvarchar(1000))
if not exists(select 1 from tab_formula where formula=@formula) insert into tab_formula(formula) values(@formula)

declare c cursor for select id,val from tab_values
declare @script as nvarchar(4000)
open c
fetch c into @id,@val
while @@fetch_status=0
set @script =
'if not exists(select 1 from syscolumns c inner join sysobjects o on c.id=o.id where o.name=''tab_formula'' and c.name=''id'+convert(nvarchar(3),@id)+
''') alter table tab_formula add id'+convert(nvarchar(3),@id)+ ' decimal(10,2)'
print @script
set @script='update tab_formula  set id'+convert(nvarchar(3),@id)+'='+convert(nvarchar(10),@val)+' where formula='''+@formula+''''
print @script
fetch c into @id,@val
close c
deallocate c

set @script='select *,convert(decimal(10,2),'+@formula+') "Result" from tab_formula where formula='''+@formula+''''
print @script



exec usp_evaluate_formula_KAMEL'10*id1-id2*id3/id4'


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend