CODEWITHSUNDEEP
sqlsql-server
Marcin Ł.
Marcin Ł.

Reputation: 45

SQL Calculating balance based on inventory and transactions

I am creating a stored procedure that calculates a users inventory status.

Imagine the following table called user_inventory with many numbered columns:

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

And another one called 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

What I would like is a way to calculate the remaining inventory status for each column after subtracting a sum of all of the users transactions, like so:

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

The additional obstacle is that there are columns labeled from 0 to 499.

I have tried to use a while loop and update one column at a time using dynamic sql and SUM(), but had both scope and performance issues - and I'm not sure if that was a good approach to this problem. I am using 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

This returned Must declare the scalar variable "@amount".multiple times. I am aware this is an ugly approach, any suggestions to this problem are greatly appreciated.

Upvotes: 2

Views: 846

Answers (1)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

You are getting the error because you are using the variables outside the scope of the variable. Query strings are executed as a separate session, so you need to declare the variables inside the query string.

You can try this by declaring the variables inside the query string

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

Upvotes: 1

Related Questions