title: SQL Server 2005 Table Variable Update Problem
body: <p>I have been reading about the differences between Table Variables and Temp Tables and stumbled upon the following issue with the Table Variable.  I did not see this issue mentioned in the articles I pursued. </p>
  
  <p>I pass in a series of PKs via a XML data type and successfully create the records in both temp table structures.  When I attempt to update further fields in the temp tables the Table Variable fails but the Temp Table has no problem with the Update Statement.  What do need to do different?  I would like to take advantage of the speed boost that Table Variables promise…</p>
  
  <p>Here are the SP snippets and Results:</p>
  
  <pre><code>CREATE PROCEDURE ExpenseReport_AssignApprover
  (
      @ExpenseReportIDs       XML
  )
  AS
  
  
  DECLARE     @ERTableVariable        TABLE   (   ExpenseReportID             INT,
                                                  ExpenseReportProjectID      INT,
                                                  ApproverID                  INT)
  
  
  CREATE TABLE #ERTempTable
  (
      ExpenseReportID             INT,
      ExpenseReportProjectID      INT,
      ApproverID                  INT
  )
  
  INSERT INTO @ERTableVariable (ExpenseReportID)
  SELECT ParamValues.ID.value('.','VARCHAR(20)')
  FROM @ExpenseReportIDs.nodes('/Root/ExpenseReportID') as ParamValues(ID)
  
  INSERT INTO #ERTempTable (ExpenseReportID)
  SELECT ParamValues.ID.value('.','VARCHAR(20)')
  FROM @ExpenseReportIDs.nodes('/Root/ExpenseReportID') as ParamValues(ID)
  
  UPDATE          #ERTempTable
  SET             ExpenseReportProjectID = (  SELECT TOP 1 ExpenseReportProjectID 
                                                  FROM ExpenseReportItem 
                                                  WHERE(ExpenseReportID = #ERTempTable.ExpenseReportID))
  
  UPDATE          @ERTableVariable
  SET             ExpenseReportProjectID = (  SELECT TOP 1 ExpenseReportProjectID 
                                                  FROM ExpenseReportItem 
                                                  WHERE(ExpenseReportID = @ERTableVariable.ExpenseReportID))
  </code></pre>
  
  <p>Error when last update statement in there :
  Must declare the scalar variable "@ERTableVariable".</p>
  
  <p>ExpenseReportProjectID is updated in #ERTempTable when the last update is commented out:</p>