Camp - 1 year ago 55

SQL Question

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.

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