Camp - 1 year ago 66

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.

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

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

Recommended from our users: **Dynamic Network Monitoring from WhatsUp Gold from IPSwitch**. ** Free Download**