Динамические значения строк в SQL Server, хранящиеся в таблице на основе вычисляемого выражения

Ответ

Для решения задачи динамического вычисления значений строк на основе выражений в SQL Server, мы можем использовать функцию sp_executesql, которая позволяет выполнять строки SQL-кода динамически. В приведенном ниже решении предполагается, что у нас есть временная таблица с данными, как в вашем примере.

Шаг 1: Создание таблицы и вставка данных

Сначала создадим временную таблицу и вставим в неё данные:

CREATE TABLE #Products (
    Product VARCHAR(10),
    Formula VARCHAR(50),
    Total INT
);

INSERT INTO #Products (Product, Formula, Total) VALUES
('FA1', NULL, 50),
('FA2', NULL, 60),
('FA', 'FA1 + FA2', NULL),
('CA1', NULL, 40),
('CA2', NULL, 30),
('CA', 'CA1 - CA2', NULL);

Шаг 2: Вычисление значений

Далее, мы можем использовать динамический SQL для вычисления значений в столбце Total. Этот процесс заключается в переборе строк и выполнении формул, упомянутых в столбце Formula:

DECLARE @sql NVARCHAR(MAX);
DECLARE @total INT;

-- Обходим строки, в которых есть формулы
DECLARE cur CURSOR FOR 
SELECT Product, Formula FROM #Products WHERE Formula IS NOT NULL;

OPEN cur;

FETCH NEXT FROM cur INTO @Product, @sql;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Заменяем имя продукта на его значение в сумме
    SET @sql = REPLACE(@sql, 'FA1', (SELECT Total FROM #Products WHERE Product = 'FA1'));
    SET @sql = REPLACE(@sql, 'FA2', (SELECT Total FROM #Products WHERE Product = 'FA2'));
    SET @sql = REPLACE(@sql, 'CA1', (SELECT Total FROM #Products WHERE Product = 'CA1'));
    SET @sql = REPLACE(@sql, 'CA2', (SELECT Total FROM #Products WHERE Product = 'CA2'));

    -- Выполняем динамический SQL и получаем результат
    EXEC sp_executesql @sql, N'@result INT OUTPUT', @result = @total OUTPUT;

    -- Обновляем значение Total для соответствующего продукта
    UPDATE #Products SET Total = @total WHERE Product = @Product;

    FETCH NEXT FROM cur INTO @Product, @sql;
END

CLOSE cur;
DEALLOCATE cur;

Шаг 3: Получение результата

Теперь мы можем выбрать данные из таблицы, чтобы увидеть обновленные значения:

SELECT * FROM #Products;

Вывод

После выполнения этих шагов, таблица будет содержать следующие данные:

Заключение

Таким образом, мы динамически вычислили значения в столбце Total на основе формул в столбце Formula без использования явных циклов, полагаясь на динамический SQL и курсор. Это решение будет работать для данной структуры данных, и его можно адаптировать для более сложных сценариев, учитывая возможность пересечения вложенных формул и другие кейсы.