sql-server - UPDATE 语句使用 EXEC 执行存储过程来 SET 列值

Question

我即将完成将基于游标的存储过程更改为基于集的存储过程。几乎，因为我只剩下一件事需要弄清楚。

他们使用名为GetSequence的存储过程来查询表，用新的序列号(old + 1)更新它并返回新的序列号值。当他们使用游标时这不是问题，因为他们将输出值分配给变量，然后使用该变量。

我能想到的保持新存储过程集基于的唯一方法是在 INSERT 或 UPDATE 语句中执行 GetSequence。然而，当我尝试这样做时，我得到了一个非常具体的错误，“关键字“EXEC”附近的语法不正确”。

这是旧代码:

  DECLARE @new_UD_campaignID BIGINT -- Get the new ud_lead_id for the new lead set
  EXEC ppGlobal.dbo.Getsequence
    'ud_campaign_id',
    @new_UD_campaignID OUTPUT
  DECLARE @OrderNum VARCHAR(9);
  IF @corpCamp LIKE '%LEP%'
    BEGIN
        SELECT @OrderNum = ( 'L' + RIGHT('00000000' + CAST(@new_UD_campaignID AS VARCHAR(8)), 8) )
    END
  ELSE
    BEGIN
        SELECT @OrderNum = ( 'C' + RIGHT('00000000' + CAST(@new_UD_campaignID AS VARCHAR(8)), 8) )
    END

这可行，但速度非常慢，因为它位于游标中并更新超过两百万行。

我正在尝试的新代码如下所示:

  UPDATE @List
  SET   OrderNumBigInt = EXEC (ipCore.dbo.Getsequence
                                     'ud_campaign_id',
                                     @new_UD_campaignID OUTPUT)

我找不到任何具体文档表明您无法在 SELECT 或 UPDATE 语句中执行存储过程来设置列值。

有人尝试过类似的方法，但取得了成功吗？

Answer 1

您所建议的内容无法在 MSSQL 中完成(据我所知)。事实上，我怀疑将 GetSequence 转换为函数的建议可能也行不通，因为最新的 ud_campaing_id 可能存储在某个“全局”表中......

假设 GetSequence 存储过程被不同的进程“同时”调用，我建议您要么

• 需要调整所说的sp，这样你就可以一次请求一堆代码(额外的参数，例如@number_of_ids，默认为1)，以便输出参数返回请求的第一个id，但在内部也保留接下来的n个id为您提供，然后您可以用它来更新您的@list
• 需要创建一个紧密循环来获取 id 的编号，然后将它们一次性应用到目标表。

尽管我肯定支持前一种解决方案，但它需要对似乎非常核心的存储过程进行更改，而 dba 可能不喜欢或不允许这样做。尽管如此，它会让事情变得更快。第二个解决方案仍然需要一些循环，并且在将结果数据应用到结束表时也有一些严格的索引要求，因此它远非完美，但至少可能比直接循环快一点目标表并按记录获取并应用新数据记录。

根据您使用的 UPDATE @list 方法判断，我认为您已经走上第二条建议的轨道。假设您在 @list 中有一个身份字段(对其具有 UNIQUE OR PK 约束并且没有间隙)，您可以尝试以下操作:

DECLARE @RecordID, @LastRecordID int
DECLARE @new_UD_campaignID bigint

SELECT @RecordID = Min(RecordID), 
       @LastRecordID = Max(RecordID)
  FROM @list

DECLARE @newCampaingIDs TABLE (RecordID int PRIMARY KEY, new_UD_campaignID varchar(8))

WHILE @RecordID <= @LastRecordID
    BEGIN

        EXEC ppGlobal.dbo.Getsequence 'ud_campaign_id', @new_UD_campaignID OUTPUT

        INSERT @newCampaingIDs (RecordID, new_UD_campaignID) VALUES (@RecordID, RIGHT('00000000' + CAST(@new_UD_campaignID AS VARCHAR(8)), 8))

        SELECT @RecordID = @RecordID + 1
    END

UPDATE @list
   SET OrderNum = (CASE WHEN corpCamp LIKE '%LEP%' THEN 'L' ELSE 'C' END) + new_UD_campaignID
  FROM @list upd
  JOIN @newCampaingIDs new
    ON new.RecordID = upd.RecordID

我认为这会更快的原因是因为顺序插入比按记录更新原始表记录的开销要少(很多？)。话又说回来，您仍然陷入重复调用 GetSequence 存储过程的困境，这可能是您的主要时间消耗者。

无论如何，唯一确定的方法就是通过测试 =)

祝你好运。