Арифметическая/логическая операция на основе значения столбца

Я хочу выполнить арифметическую операцию, основанную на моих значениях столбца. Рассмотрим следующий пример

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

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

Ожидаемый результат:

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

Я могу сделать это с помощью оператора CASE.

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

Но есть ли способ сделать это динамически. Оператор может быть чем-то вроде /, %, *. Что-то вроде этого

DECLARE @sql NVARCHAR(max)=''

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

--PRINT @sql

EXEC Sp_executesql
  @sql

Что явно не работает, говоря

Msg 102, уровень 15, состояние 1, строка 1 Неверный синтаксис рядом 'Check_value'.

Ответ 1

Отличный вопрос.

Я бы использовал выражение case, потому что:

Есть только пять арифметических операторов.
Вы не можете параметризовать арифметические операторы напрямую.
Вы не можете выполнить динамический SQL inline.

Но есть альтернативы. Вы можете создать и выполнить динамический оператор SQL.

-- 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);

Или вы можете использовать крест для вычисления результата с использованием грубой силы.

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
;

Здесь вычисляется всякая возможная операция. Те, которые не требуются, отфильтровываются из набора результатов. Этот подход легче читать и писать, но выполняет вычисления, которые никогда не требуются. Тем не менее, это привело к созданию более быстрого плана запросов в моем динамическом примере.

ИЗМЕНИТЬ

Благодаря @Damien_The_Unbeliever, который указал на мою уязвимость для деления на нулевые ошибки. Я использовал NULLIF для замены 0s для нулей, что позволяет избежать ошибки.

Я только обновил второй пример.

Ответ 2

Отказ от ответственности: Я являюсь владельцем проекта Eval SQL.NET

Эта библиотека позволяет использовать синтаксис С# непосредственно в T-SQL для оценки динамического арифметического выражения. Приоритет и скобки операторов соблюдаются, и библиотека выходит за рамки простого математического выражения.

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

Документация: SQL Server Eval - динамическая оценка арифметической операции и выражения