Arithmetic/Logical operation's based on Column value

Question

I want to do arithmetic operation based on my column values. Consider the following example

CREATE TABLE #test
  (
     cont_sal    INT,
     check_value INT,
     operator    VARCHAR(50)
  )

INSERT #test
VALUES (10,20,'+'),
       (20,10,'+'),
       (10,20,'-'),
       (20,10,'-') 

Expected Result:

cont_sal    check_value result
--------    ----------- ------
10          20          30
20          10          30
10          20          -10
20          10          10

I can do this using CASE statement.

SELECT cont_sal,
       check_value,
       CASE
         WHEN operator = '+' THEN cont_sal + check_value
         when operator = '-' THEN cont_sal - check_value
       END result
FROM   #test 

But is there any way to do this dynamically. Operator's can be anything like /,%,*. Something like this

DECLARE @sql NVARCHAR(max)=''

SET @sql = 'select cont_sal ' + 'operator'
           + ' check_value from #test '

--PRINT @sql

EXEC Sp_executesql
  @sql 

Which obviously didn't work saying

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'check_value'.

Answer 1

Great question.

I would use a case expression because:

1. There are only five arithmetic operators.
2. You cannot parametrise arithmetic operators, directly.
3. You cannot execute dynamic SQL inline.

But there are alternatives. You could build and then execute a dynamic SQL statement.

-- Query will be stored here.
DECLARE @Qry VARCHAR(255) = '';

-- Build up the query.
SELECT
    @Qry = 
        @Qry 
        +   CASE ROW_NUMBER() OVER (ORDER BY cont_sal)
                WHEN 1 THEN 'SELECT '
                ELSE 'UNION ALL SELECT '
            END 
        + ''''
        + Expression 
        + ''''
        + ' AS Expression,'
        + Expression
        + ' AS Result '
FROM
    #test AS t
        CROSS APPLY
            (
                -- Avoid typing expression twice.
                SELECT
                    CAST(cont_sal AS VARCHAR(50)) 
                        + ' '
                        + operator 
                        + ' '
                        + CAST(check_value AS VARCHAR(50)) AS Expression        
            ) AS ex
;

-- Execute it.
EXECUTE(@Qry);

Or you could use a cross apply to calculate the result using brute force.

SELECT
    *       
FROM
    #test AS t

        CROSS APPLY
            (
                VALUES
                    ('+', cont_sal + check_value),
                    ('-', cont_sal - check_value),
                    ('*', cont_sal * check_value),
                    ('/', cont_sal / NULLIF(check_value, 0)),
                    ('%', cont_sal % NULLIF(check_value, 0))
            ) AS ex(operator, result)
WHERE
    ex.operator = t.operator
;

Here every possible operation is calculated. Those that are not required are filtered out of the result set. This approach is easier to read and write, but runs calculations that are never required. That said, it did generate a faster query plan that my dynamic example.

EDIT

Thanks to @Damien_The_Unbeliever, who pointed out my vulnerability to divide by zero errors. I've used NULLIF to swap out 0s for nulls, which avoids the error.

I've only updated the 2nd example.

Answer 2

Disclaimer: I'm the owner of the project Eval SQL.NET

This library allow to use C# syntax directly in T-SQL to evaluate dynamically arithmetic expression. Operator precedence and parenthesis are honored and the library go way beyond simple mathematic expression.

CREATE TABLE #test
    (
      cont_sal INT ,
      check_value INT ,
      operator VARCHAR(50)
    )

INSERT  #test
VALUES  ( 10, 20, '+' ),
        ( 20, 10, '+' ),
        ( 10, 20, '-' ),
        ( 20, 10, '-' )

DECLARE @sqlnet SQLNET = SQLNET::New('')

SELECT  cont_sal ,
        check_value ,
        @sqlnet.Code('x ' + operator + ' y')
           .Val('x', cont_sal)
           .Val('y', check_value)
           .Eval()
FROM    #test

DROP TABLE #test

Documentation: SQL Server Eval - Dynamically evaluate arithmetic operation and expression