title: How to assign a select result to a variable?
body: <p>How do I store a selected field value into a variable from a query and use it in an update statement?</p>
  
  <p>Here is my procedure:</p>
  
  <p>I'm writing a SQL Server 2005 T-SQL stored procedure which does the following:</p>
  
  <ol>
  <li>gets list of invoices id's from invoice table and stores to Cursor </li>
  <li>Fetch invoice id from cursor -> tmp_key variable </li>
  <li>foreach tmp_key finds invoice client primary contact id from customer table</li>
  <li>updates the client contact key with primary contact id </li>
  <li>close cursor</li>
  </ol>
  
  <p>Here is my code: </p>
  
  <pre><code>DECLARE @tmp_key int
  DECLARE @get_invckey cursor 
  
  set @get_invckey = CURSOR FOR 
      select invckey from tarinvoice where confirmtocntctkey is null and tranno like '%115876'
  
  OPEN @get_invckey 
  
  FETCH NEXT FROM @get_invckey into @tmp_key
  
  WHILE (@@FETCH_STATUS = 0) 
  BEGIN 
      SELECT c.PrimaryCntctKey as PrimaryContactKey
      from tarcustomer c, tarinvoice i
      where i.custkey = c.custkey and i.invckey = @tmp_key
  
      UPDATE tarinvoice set confirmtocntctkey = PrimaryContactKey where invckey = @tmp_key
      FETCH NEXT FROM @get_invckey INTO @tmp_key
  END 
  
  CLOSE @get_invckey
  DEALLOCATE @get_invckey
  </code></pre>
  
  <p>How do I store the PrimaryContactKey and use it again in the set clause of the following update statement?  Do I create a cursor variable or just another local variable with an int type?</p>