SQL Server 2005 Table Variable Update Problem

Question

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.

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…

Here are the SP snippets and Results:

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))

Error when last update statement in there : Must declare the scalar variable "@ERTableVariable".

ExpenseReportProjectID is updated in #ERTempTable when the last update is commented out:

Answer 1

A quick test works when I literalize the table var reference in the last update:

UPDATE @ERTableVariable
    SET ExpenseReportProjectID = (      
        SELECT TOP 1 ExpenseReportProjectID
        FROM ExpenseReportItem 
        WHERE ExpenseReportID = [@ERTableVariable].ExpenseReportID
    )

You could also use an 'update from':

UPDATE er SET 
    ExpenseReportProjectID = ExpenseReportItem.ExpenseReportProjectID
FROM @ERTableVariable er
INNER JOIN ExpenseReportItem ON 
    ExpenseReportItem.ExpenseReportID = er.ExpenseReportID

The join might return multiple rows but only one will 'stick'. Kind of a non-deterministic update like 'TOP 1'.