代码之家 › 专栏 › 技术社区 › DiningPhilanderer

SQL Server 2005表变量更新问题

sql-server-2005

8

DiningPhilanderer · 技术社区 · 17 年前

我一直在读关于表变量和临时表之间的差异的书,偶然发现了以下关于表变量的问题。我没有看到在我的文章中提到这个问题。

我通过XML数据类型传入了一系列PK,并成功地在两个临时表结构中创建了记录。当我尝试更新temp表中的更多字段时,Table变量失败,但temp表的update语句没有问题。你需要做什么不同的事情?我想利用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中更新:

2 回复 | 直到 16 年前

1

16

Corbin March 17 年前

当我在上一次更新中对表var引用进行文字化时,快速测试会起作用:

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

您还可以使用“更新自”:

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

联接可能返回多行,但只有一行会“粘住”。有点像“TOP1”这样的非确定性更新。

2

0

TcKs 17 年前

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