SQL Расчет баланса на основе инвентаризации и транзакций

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

Представьте себе следующую таблицу с именем user_inventory со многими пронумерованными столбцами:

id_inventory id_user 0 1 2 3
------------ ------- - - - -
 2 4 5 0 14 21

И еще один пользователь user_transactions

id_tran id_user 0 1 2 3
------- ------- - - - -
 54 4 1 0 3 7
 55 4 2 0 9 8
 56 4 1 0 2 4

То, что я хотел бы, это способ рассчитать оставшийся статус инвентаря для каждого столбца после вычитания суммы всех транзакций пользователей, например:

id_availableInventory id_user 0 1 2 3
--------------------- ------- - - - -
 2 4 1 0 0 2

Дополнительным препятствием является наличие столбцов с отметками от 0 до 499.

Я попытался использовать цикл while и обновлять один столбец за раз с использованием динамических sql и SUM(), но имел проблемы с охватом и производительностью, и я не уверен, что это был хороший подход к этой проблеме. Я использую SQL Server 2012.

DECLARE @counter int 
DECLARE @userid int
DECLARE @amount int
DECLARE @sum int
declare @sql nvarchar(1000)
SET @counter = 0 
SET @userid = 4 
WHILE @counter < 500 
BEGIN 
 set @sql = 'SELECT @amount = [' + CAST(@counter AS nvarchar) + '] FROM user_inventory WHERE ID_User = ' +CAST(@userid AS nvarchar)
 EXEC(@sql)
 set @sql = 'SELECT @sum = SUM([' + CAST(@counter AS nvarchar) + ']) FROM user_transactions WHERE ID_User = ' +CAST(@userid AS nvarchar)
 EXEC(@sql)
 set @sql = 'UPDATE user_availableinventory SET [' + CAST(@counter AS nvarchar) + '] = @amount - @sum WHERE ID_User = ' +CAST(@userid AS nvarchar)
 EXEC(@sql)
 SET @counter = @counter + 1 
END

Это возвращается. Must declare the scalar variable "@amount". много раз. Я знаю, что это уродливый подход, любые предложения по этой проблеме очень ценятся.

1 ответ

Вы получаете ошибку, потому что используете переменные за пределами области действия переменной. Строки запроса выполняются как отдельный сеанс, поэтому вам нужно объявить переменные внутри строки запроса.

Вы можете попробовать это, объявив переменные внутри строки запроса

DECLARE @counter int 
DECLARE @userid int
declare @sql nvarchar(1000)
SET @counter = 0 
SET @userid = 4 
WHILE @counter < 500 
BEGIN 
 set @sql = '
 DECLARE @sum int 
 DECLARE @amount int

 SELECT 
 @amount = [' + CAST(@counter AS nvarchar) + '] 
 FROM user_inventory WHERE ID_User = ' +CAST(@userid AS nvarchar)+'

 SELECT 
 @sum = SUM([' + CAST(@counter AS nvarchar) + ']) 
 FROM user_transactions WHERE ID_User = ' +CAST(@userid AS nvarchar)+'

 UPDATE user_availableinventory SET [' + CAST(@counter AS nvarchar) + '] = @amount - @sum WHERE ID_User = ' +CAST(@userid AS nvarchar)

 EXEC(@sql)
 SET @counter = @counter + 1 

END

licensed under cc by-sa 3.0 with attribution.