CODEWITHSUNDEEP
sqlsql-servert-sql
Camp
Camp

Reputation: 97

Calculating/Evaluate user defined formula with SQL

I have a table that is holding user defined calculation formulas and another table that holds the values. I need to inject the value into the calculation formula to be able to evaluate the formula and return the result.

Example formula that is in a column row called Calculation formula in the Formulas table

(310165)/(7248+7249)

These numbers represent corresponding ValueID's in the Values table

ValueID | Value
7248    | 18521
7249    | 3835
310165  | 68546.24

I need to find a way with SQL to inject the Value into the formula in the Formulas table by matching up with the ValueID in the Values table.

I have looked at replace and stuff and for xml path and I cant seem to find a way to get this done. After this is done I then need to evaluate the expression that has been produced with the actual values to return an int value. Any help is greatly appreciated.

Upvotes: 1

Views: 400

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

This Update will process the entire table dynamically. Notice in @Formulas, I added an ID and a second demonstrative record

Declare @Formulas table (ID int,Calculation varchar(max))
Insert Into @Formulas values
(1,'([310165])/([7248]+[7249])'),
(2,'([999999])/([7248]+[7249])')

Declare @Values table (ValueID int,Value money)
Insert Into @Values values
(7248    , 18521),
(7249    , 3835),
(310165  , 68546.24),
(999999  , 75000)

Declare @SQL varchar(max)=''
Select  @SQL = @SQL+concat(',(',ID,',',Calculation,')') From @Formulas --Where ID=2
Select  @SQL = Replace(@SQL,'['+cast(ValueID as varchar(25))+']',Value) From @Values
Select  @SQL = 'Select * From ('+Stuff(@SQL,1,1,'values')+') N(ID,Value)'
Exec(@SQL)

Returns

ID  Value
1   3.06612274109
2   3.35480407944

Upvotes: 1

Related Questions