SQL Server 2005表变量更新问题

我一直在阅读表变量和临时表之间的差异,并偶然发现表变量的以下问题.我没有看到我追求的文章中提到的这个问题.

我通过XML数据类型传递一系列PK,并在两个临时表结构中成功创建记录.当我尝试更新临时表中的其他字段时,表变量失败,但临时表没有更新语句的问题.需要做些什么？我想利用Table Variables承诺的速度提升......

以下是SP片段和结果:

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

其中的最后一个更新语句出错:必须声明标量变量"@ERTableVariable".

当最后一次更新被注释掉时,ExpenseReportProjectID在#ERTempTable中更新:

当我在上次更新中对表var参考进行文字化时,快速测试有效:

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

您还可以使用'update from':

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

连接可能会返回多行,但只有一行会"粘住".一种像'TOP 1'这样的非确定性更新.