title: Using While Loop for SQL Server Update
body: <p>I am trying to become more efficient in my SQL programming.
  I am trying to run a loop to repeat an update command on field names that only change by a numerical suffix.</p>
  
  <p>For example, instead of writing out <code>x_1, y_1</code>, then <code>x_2, y_2</code> for each update:</p>
  
  <pre><code>DECLARE @a INT 
  DECLARE @b VARCHAR 
  
  SET @a = 1
  WHILE @a &lt; 30
  set @b = @a
    BEGIN
         UPDATE source set h = h + "x_"+@b
             where "y_"+@b = 'Sold'
      SET @a = @a + 1
    END
  </code></pre>
  
  <p>Let me know if I can clarify. I'm using SQL Server 2005.</p>
  
  <p>Thanks for any guidance.</p>
  
  <hr>
  
  <p>I'm trying to apply Adams's solution and need to understand what is proper usage of N' in the following:</p>
  
  <pre><code>exec sp_executesql update source_temp set pmt_90_day = pmt_90_day + convert(money,'trans_total_'+@b'')
      where convert(datetime,'effective_date_'+@b) &lt;= dateadd(day,90,ORSA_CHARGE_OFF_DATE)
      and DRC_FLAG_'+@b = 'C'
  </code></pre>