0

I have formula table with some of the formula. Is it possible to write SQL stored procedure which could evaluate every formula and give the result ? Every formula is an expression which could have all different arithmetic operations. Every value used in formula is an id value referring to some other table

FormulaId     | Formula                       
--------------+-------------                  
1             | `1 * 3`                       
2             | `(2 + 3) * (4 + 1)`           
3             | `((2 + 3) * (4 + 1)) / 5`     
4             | `(4 + 1) - (3 + 1) - (2 + 1)` 

Id            | Value
--------------+-------------
1             | 5
2             | 10
3             | 15
4             | 20
5             | 25

Result should be something like

FormulaId     | EvaluatedValue  
--------------+----------------
1             | 75
2             | 625
3             | 25
4             | -10
Prasad Koranne
  • 45
  • 1
  • 8
  • Possible duplicate of [Storing formula (equations) in database to be evaluated later (SQL Server 2005)](https://stackoverflow.com/questions/9722782/storing-formula-equations-in-database-to-be-evaluated-later-sql-server-2005) – ventiseis Sep 14 '17 at 18:11
  • There's not a great way to do what you're describing. I sense this is sort of a toy example; can you expand on your use case a little? There may be an alternative to having to store an arbitrary formula and have it returned like a computed column. – Xedni Sep 14 '17 at 18:23
  • There is no alternative to store this formula as of now as we have UI which is allowing users to create formula of their own (by selecting some drop down values in UI) and we are storing ids of the dropdown values equivalent into the table. These formulas created by users needs to be used at few places for calculation. One alternative we have is to write C# code to achieve formula evaluation but because this approach is time consuming and we will have a million records to process against the formula, we are looking out for database approach – Prasad Koranne Sep 14 '17 at 18:32
  • Also, it is unclear how the "Formula table" and "Value table" relate to each other. I don't see anything in the Formula entries where a value would plug in. – Brian Sep 14 '17 at 18:34
  • 1
    the numbers in the formulas look like tags to me, and the ValueID would then replace the "tag" with it's value – Xedni Sep 14 '17 at 18:35
  • 1
    Consider the formula 1 * 3, 1 = 5 and 3 = 15 in the value table, so the evaluated value would be 75 – Prasad Koranne Sep 14 '17 at 19:04

2 Answers2

0

Here is how you can evaluate an expression stored in a table:

declare @SQL nvarchar(max) 

Declare @Result As Int 
Select @SQL = Formula
    From dbo.Formulae
    Where ID = 3
Set @SQL = 'select @x = ' + @SQL

exec sp_executesql @SQL, N'@x int out', @Result out

select @Result

Assuming that Xendi and Prasad are correct and the numbers in the Formula entry are keys used to look up values from the Values table, then you'll have to define the delimiters used in your formula strings and then write a routine to parse the formulae and do the substitution. Then you'd feed the resulting string into the logic I wrote above.

All that being said, TSQL is not the right tool for this job.

Brian
  • 1,238
  • 2
  • 11
  • 17
0

I'd strongly recommend doing this in your application rather than SQL. I have a solution, but it's ridiculously involved. I would create a proc which returns the formula, plus the values that need to be replaced. Then use .Replace() or a regex library to replace the tags with the values (Note, you're going to run into trouble if your tags are integers; consider wrapping them in something like some kind of bracket). You can then use one of several techniques to evaluate the expression at run time (see Evaluating string "3*(4+2)" yield int 18 for some ideas).

Note, I listed stuff primarily in C#, since that's what I know; but if you're using a different language, there should be something equivalent.

Xedni
  • 3,662
  • 2
  • 16
  • 27