Camp Camp - 1 year ago 66
SQL Question

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


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.

Answer Source

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

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)'


ID  Value
1   3.06612274109
2   3.35480407944
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download