SQL Query to use column as a formula to calculate value

Question

I am working on a request that i need to calculate value based on formula specified in another column

Below is my table:

I need to write the query to get value which will be based on FORMULA column. eg I need result as

As formula could be anything consisting of my columns PRICE and SIZE, how do i write the query to achieve this?

There is no simple way to do this. One approach would use a cursor and dynamic SQL. — Gordon Linoff, Aug 07 '17 at 12:23
Why not set value to calculated column in table during update? if Update happening not in SQL — ASpirin, Aug 07 '17 at 12:41
Even in SQL e.g onupdate trigger can run dynamic sql to calculate value and put into the column — ASpirin, Aug 07 '17 at 12:43

Nenad Zivkovic · Answer 1 · 2017-08-08T06:44:09.257

Dynamic query is the (only) way to go and it's not that complicated:

DECLARE @query NVARCHAR(MAX) = '';

SELECT @query = @query + '
UNION 
SELECT ItemID, Price, Size, Formula, ' + Formula + ' AS CalcValue FROM YourTable WHERE Formula = ''' + Formula + ''' ' 
FROM YourTable;
SET @query = STUFF(@query,1,8,'');

PRINT @query;

EXEC (@query);

SQLFiddle DEMO

But you must be aware how prone to errors this is. If value of Formula column is not valid formula query breaks.

edit: going with UNION instead of UNION ALL because of same formula appearing in multiple rows

edit2: Plan B - Instead of running bunch of same select queries and making distinct of results, better to make distinct formulas at beginning:

DECLARE @query NVARCHAR(MAX) = '';

WITH CTE_DistinctFormulas AS 
(
    SELECT DISTINCT Formula FROM YourTable
)
SELECT @query = @query + '
UNION ALL 
SELECT ItemID, Price, Size, Formula, ' + Formula + ' AS CalcValue FROM YourTable WHERE Formula = ''' + Formula + ''' ' 
FROM CTE_DistinctFormulas;
SET @query = STUFF(@query,1,12,'');

PRINT @query;

EXEC (@query);

SQLFiddle DEMO 2 - added few more rows

score 1 · Accepted Answer · answered Aug 07 '17 at 14:00

Another alternative which is relatively easy to do is with a CLR. You can take advantage of the Compute Method of DataTable to give a simple one line code in C#.

[Microsoft.SqlServer.Server.SqlFunction]
public static double Evaluate(SqlString expression)
{
    return double.Parse((new DataTable()).Compute(expression.ToString(), "").ToString());
}

Then add the assembly to SQL Server and create the wrapper function:

CREATE FUNCTION [dbo].[Evaluate](@expression [nvarchar](4000))
RETURNS [float] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [YourAssemblyName].[YourClassName].[Evaluate]
GO

Now you can call the function as part of a simple select statement:

SELECT itemid, price, size, formula, 
dbo.Evaluate(REPLACE(REPLACE(formula, 'PRICE', FORMAT(price,'0.00')), 
'SIZE', FORMAT(size, '0'))) as calcvalue FROM YourTable

Glad to have helped. In my experience CLR functions are very quick BUT I have never had to use one on a table with more than about 60-70,000 records, so I really would not like to say what is the quickest solution. I'm afraid I am very much of the school "Don't fix, what ain't broke". If I find a solution that works for me, I tend not to go looking for another :-) — Jonathan Willcock, Aug 08 '17 at 07:52

score 0 · Answer 3 · answered Aug 07 '17 at 22:26

I did something similar to this and if you play in a smaller pool of operations it is not that hard. I went with a series where I had X and Y columns and an operator. Then I just did a large case when statement on identifying the operator and performing logic based on that. You may have to change your structure slightly. The core of the problem is that SQL is a result set based engine so anything where you have to do an operation to determine dynamic is going to be slower. EG:

DECLARE @Formula TABLE 
(
  FormulaId INT IDENTITY
, FormulaName VARCHAR(128)
, Operator VARCHAR(4)
);

DECLARE @Values TABLE 
( 
  ValueId INT IDENTITY
, FormulaId INT
, Price MONEY
, Size INT
)

INSERT INTO @Formula (FormulaName, Operator)
VALUES ('Simple Addition', '+'), ( 'Simple Subtraction', '-'), ('Simple Multiplication', '*'), ('Simple Division', '/'), ('Squared', '^2'), ('Grow by 20 percent then Multiply', '20%*')

INSERT INTO @Values (FormulaId, Price, Size)
VALUES (1, 10, 5),(2, 10, 5),(3, 10, 5),(4, 10, 5),(5, 10, 5),(6, 10, 5),(1, 16, 12),(6, 124, 254);

Select *
From @Values

SELECT
  f.FormulaId
, f.FormulaName
, v.ValueId
, Price
, Operator
, Size
, CASE WHEN Operator = '+' THEN Price + Size 
      WHEN Operator = '-' THEN Price - Size 
      WHEN Operator = '*' THEN Price * Size
      WHEN Operator = '/' THEN Price / Size
      WHEN Operator = '^2' THEN Price * Price
      WHEN OPerator = '20%*' THEN (Price * 1.20) * Size
      END AS Output
FROM @Values v
  INNER JOIN @Formula f ON f.FormulaId = v.FormulaId

With this method my operation is really just a pointer reference to another table that has an operator that is really for all intents and purposes just a token I use for my case statement. You can even compound this potentially and if you wanted to do multiple passed you could add a 'Group' column and a 'Sequence' and do one after the other. It depends how difficult your 'formulas' become. Because if you get into more than 3 or 4 variables that change frequently with frequent operator changes, then you probably would want to do dynamic sql. But if they are just a few things, that should not be that hard. Just keep in mind the downside of this approach is that it is hard coded at a certain level, yet flexible that the parameters put into it can apply this formula over and over.

SQL Query to use column as a formula to calculate value

3 Answers3

Linked