title: SQL Calculating balance based on inventory and transactions
body: <p>I am creating a stored procedure that calculates a users inventory status.</p>
  
  <p>Imagine the following table called <code>user_inventory</code> with many numbered columns:</p>
  
  <pre><code>id_inventory    id_user    0    1    2    3
  ------------    -------    -    -    -    -
             2          4    5    0    14   21
  </code></pre>
  
  <p>And another one called <code>user_transactions</code></p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>id_availableInventory    id_user    0    1    2    3
  ---------------------    -------    -    -    -    -
                      2          4    1    0    0    2
  </code></pre>
  
  <p>The additional obstacle is that there are columns labeled from 0 to 499.</p>
  
  <p>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. </p>
  
  <pre><code>DECLARE @counter int  
  DECLARE @userid int
  DECLARE @amount int
  DECLARE @sum int
  declare @sql nvarchar(1000)
  SET @counter = 0  
  SET @userid = 4 
  WHILE @counter &lt; 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  
  </code></pre>
  
  <p>This returned <code>Must declare the scalar variable "@amount".</code>multiple times. 
  I am aware this is an ugly approach, any suggestions to this problem are greatly appreciated.</p>