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

``````(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.

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download