Calculate row results based on values/operators (t-sql)-CodePudding

I have a database table that contains values (decimal) and operators (nvarchar). The operators are basic ( , -, *, /). For each row, I would like to perform a calculation based on the values and operators, e.g. for row 1: 1 2 4. The original table contains more than a dozen values and operators, for this reason, a case when statement seems ineffective to me (does it not get real large?).

CREATE TABLE #CalcTable
(
Value1 decimal(7),
Operator1 nchar(1),
Value2 decimal(7),
Operator2 nchar(1),
Value3 decimal(7),
Operator3 nchar(1),
Value4 decimal(7),
Operator4 nchar(1),
Value5 decimal(7),
Operator5 nchar(1),
Value6 decimal(7),
Operator6 nchar(1),
Value7 decimal(7),
Operator7 nchar(1),
Value8 decimal(7),
Operator8 nchar(1),
Value9 decimal(7),
Operator9 nchar(1),
Value10 decimal(7),
Operator10 nchar(1),
Value11 decimal(7),
Operator11 nchar(1),
Value12 decimal(7),
Operator12 nchar(1)
)

Insert into #CalcTable values(
1, ' ', 2, ' ', 4, ' ', 7, '-', 8, '*', 1, ' ', 10, ' ', 1, ' ', 20,
'-', 2, '-', 4, ' ', 12, ' ')

Select * from #CalcTable

I tried dynamic sql, but shame on me, I could solve it only for a specific row,

BEGIN
DECLARE @CalcQuery NVARCHAR(MAX)
DECLARE @Operator1 NCHAR(1)
SET @Operator1 = (select Operator1 FROM CalcTable where 
...); //some row
//more operators
SET @CalcQuery = 'SELECT Value1 '   @Operator1   ' Value2   ... FROM 
CalcTable where ... '; //some row
EXEC (@CalcQuery)
END

Is the best way to write an effective case/when query or dynamic sql? Or other way?

CodePudding user response：

Here is a (simplified) version of what you are trying to do. You can nest your functions but the priority will be ordinal (left to right) with no precedence).
If you want to make a real calculator you need something more than SQL.

create function dbo.calculator( @value1 decimal(5,2) ,  @operateur nchar(1), @value2 decimal(5,2) )
returns decimal(5,2)
as begin
declare @res decimal(5,2);
set @res = case @operateur
  when ' ' then @value1   @value2
  when '-' then @value1 - @value2
  when '*' then @value1 * @value2
  when '/' then @value1 / @value2
  end
return @res ;
end

create table calculations (
v1 decimal(5,2),
o1 nchar(1),
v2 decimal(5,2),
o2 nchar(1),
v3 decimal(5,2));

insert into calculations values
(2,' ',5,'/',6),
(64,'/',3,'*',4);

select 
dbo.calculator(v1,o1,v2),
dbo.calculator(v2,o2,v3),
dbo.calculator(
    dbo.calculator(v1,o1,v2),
    o2,
    v3)
from calculations

(No column name) | (No column name) | (No column name)
---------------: | ---------------: | ---------------:
            7.00 |             0.83 |             1.17
           21.33 |            12.00 |            85.32

db<>fiddle here

CodePudding user response：

The following solution evaluate the expression respecting the operator precedence. However, it also opens the door to SQL injection attack. The proper way to do this in production is to use a CLR function or one of the scripting languages integrated with SQL Server (R or Python).

-- Add a column to hold the result
ALTER TABLE #CalcTable
    ADD Result decimal(7)


DECLARE @Expression         nvarchar(max)
DECLARE @Result             decimal(7)

DECLARE @ParamDefinition    nvarchar(100) = '@Result decimal(7) OUTPUT'

DECLARE cur CURSOR FOR
    SELECT  'SELECT @Result = '
                  CAST(Value1 AS nvarchar)
                  Operator1
                  CAST(Value2 AS nvarchar)
                  Operator2
                  CAST(Value3 AS nvarchar)
                  Operator3
                  CAST(Value4 AS nvarchar)
                  Operator4
                  CAST(Value5 AS nvarchar)
                  Operator5
                  CAST(Value6 AS nvarchar)
                  Operator6
                  CAST(Value7 AS nvarchar)
                  Operator7
                  CAST(Value8 AS nvarchar)
                  Operator8
                  CAST(Value9 AS nvarchar)
                  Operator9
                  CAST(Value10 AS nvarchar)
                  Operator10
                  CAST(Value11 AS nvarchar)
                  Operator11
                  CAST(Value12 AS nvarchar)
            AS Expression
    FROM    #CalcTable
FOR UPDATE

OPEN cur
FETCH NEXT FROM cur INTO @Expression

WHILE @@FETCH_STATUS = 0
BEGIN
    
    EXEC sp_executesql @Expression
        , @ParamDefinition
        , @Result = @Result OUTPUT

    UPDATE #CalcTable
        SET Result = @Result
        WHERE CURRENT OF cur

    FETCH NEXT FROM cur INTO @Expression
END

CLOSE cur
DEALLOCATE cur