Cursors vs. while loop - SQLServer

Question

Let's say I have a bunch of rows in the database (in this case, SQLServer 2008) that you can use to create equations.

----------------------------------------------------- OperationID | EquationID | Operation | Amount | Order ----------------------------------------------------- 1 | 1 | + | 12 | 1 2 | 1 | + | 12 | 2 3 | 2 | / | 2 | 3 4 | 2 | + | 12 | 1 5 | 2 | - | 2 | 2 ----------------------------------------------------- 

I need to come up with a way to evaluate the equations in this table.

Equation 1:12 + 12 = 24
Equation 2: (12 - 2) / 2 = 5

I cannot think of a way to get these results without repeating the lines. The only ways I know how to do this is to use a cursor or use a temporary table and a while loop. Are there any better ways to do this? If not at all, what will work with the best cursors or loops?

Note. This is somewhat simplified, and at this stage of the project, we can only guess how the data will look. It is assumed that each "equation" will have from 100 to 1000 operations and that every day there will be several thousand "equations" that will need to be processed.

Answer 1

It has been demonstrated that a recursive CTE performs better than a loop to achieve totals. This is actually just the current quantity with a variable operator, so the performance advantage should be applied here.

A way to create a recursive CTE that behaves like a loop looks like this:

  ;WITH cte AS ( SELECT equation, number, order FROM table WHERE order = 1 UNION ALL SELECT table.equation, CASE WHEN table.operation = '+' THEN cte.number + table.number WHEN table.operation = '-' THEN cte.number - table.number END AS number, --etc. table.order FROM table INNER JOIN cte ON table.order = cte.order + 1 AND table.equation = cte.equation ) SELECT equation, number, order FROM cte OPTION (MAXRECURSION 1000); 

The first SELECT captures your leftmost number, and UNION performs the following operations on the number it returns. The maxrecursion parameter limits the number of operations in a single equation to 1000. You can, of course, set this higher.

This answer is somewhat incomplete because the final select query returns intermediate results. It is quite simple to filter.

Answer 2

I cleared / fleshed out the mootinator answer a bit and present this code here. I marked this community of answers wiki because the mootinator deserves an answer to the answer. This was the easiest way to present this code without editing its response.

 declare @equations table ( OperationID int, EquationID int, Operation char(1), Amount int, [Order] int ) insert into @equations (OperationID, EquationID, Operation, Amount, [Order]) values (1, 1, '+', 12, 1), (2, 1, '+', 12, 2), (3, 2, '/', 2, 3), (4, 2, '+', 12, 1), (5, 2, '-', 2, 2) ;with cteCalc as ( select EquationID, Amount, [Order] from @equations where [Order] = 1 union all select e.equationid, case when e.Operation = '+' then c.Amount + e.Amount when e.Operation = '-' then c.Amount - e.Amount when e.Operation = '*' then c.Amount * e.Amount when e.Operation = '/' then c.Amount / e.Amount end AS Amount, e.[Order] from @equations e inner join cteCalc c on e.EquationID= c.EquationID where e.[Order] = c.[Order] + 1 ), cteMaxOrder as ( select EquationID, MAX([Order]) as MaxOrder from cteCalc group by EquationID ) select c.EquationID, c.Amount from cteMaxOrder mo inner join cteCalc c on mo.EquationID = c.EquationID and mo.MaxOrder = c.[Order] order by c.EquationID option (maxrecursion 1000)