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

Try this:

CREATE PROCEDURE ExpenseReport_AssignApprover
(
        @ExpenseReportIDs               XML
)
AS BEGIN


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

END

Answer 2

Sorry I thought I was clear, I only included snippets from the SP. I left out all the comments and misc code so it wouldn't clutter the screen. My SP has the BEGIN and END keywords.

IE it works perfectly when I comment out the second update using the TempVariable...

Answer 3

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'.