如何在含嵌套DML更新的存储过程中调用字符串拆分函数

我有一个用于拆分字符串的XML函数SplitStrings_XML，现在要在存储过程spSavedPlaces中使用它——这个存储过程接收一个Guid字符串列表，需要通过拆分函数得到Guid列表，筛选SavedPlace表中对应的记录，将其IsOpen设为1，同时把更新后的记录插入到OutputTable里。但不清楚该把拆分函数调用放在哪里，试过在UPDATE前调用没成功，想找到正确的位置让内层WHERE能通过函数返回的表筛选数据。

拆分字符串函数代码 #

CREATE FUNCTION SplitStrings_XML
(
  @List       NVARCHAR(MAX),
  @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN 
  (  
    SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
    FROM 
  ( 
    SELECT x = CONVERT(XML, '<i>' 
      + REPLACE(@List, @Delimiter, '</i><i>') 
      + '</i>').query('.')
  ) AS a CROSS APPLY x.nodes('i') AS y(i)
);

原存储过程代码 #

CREATE PROCEDURE spSavedPlaces
@GuidList NVARCHAR(MAX)
AS
BEGIN

  SET NOCOUNT ON;

  INSERT INTO OutputTable(
    SavedPlaceId,
    [Name],
    IsOpen
  )
  SELECT
    SavedPlaceId,
    [Name],
    IsOpen
  FROM (UPDATE P
          SET
            IsOpen = 1

        OUTPUT
          INSERTED.SavedPlaceId,
          INSERTED.[Name],
          INSERTED.IsOpen

       FROM SavedPlace AS P

       -- WHERE P.SavedPlaceId IN 'the table returned by the SplitStrings_XML function'
  
  ) AS NDML -- End of nested DML

END

正确的修改方案 #

你得把拆分函数调用放在UPDATE语句的筛选逻辑里，也就是UPDATE的WHERE子句或者JOIN关联中，这样才能精准筛选出要更新的SavedPlace记录。下面提供两种可行写法：

写法1：用IN子句匹配拆分后的Guid #

直接在UPDATE的WHERE里通过IN调用拆分函数，注意要把返回的字符串转换为Guid类型：

CREATE PROCEDURE spSavedPlaces
@GuidList NVARCHAR(MAX)
AS
BEGIN

  SET NOCOUNT ON;

  INSERT INTO OutputTable(
    SavedPlaceId,
    [Name],
    IsOpen
  )
  SELECT
    SavedPlaceId,
    [Name],
    IsOpen
  FROM (UPDATE P
          SET
            IsOpen = 1
        OUTPUT
          INSERTED.SavedPlaceId,
          INSERTED.[Name],
          INSERTED.IsOpen
       FROM SavedPlace AS P
       WHERE P.SavedPlaceId IN (
           SELECT CAST(Item AS UNIQUEIDENTIFIER) 
           FROM dbo.SplitStrings_XML(@GuidList, ',') -- 传入你的Guid列表分隔符，比如逗号
       )
  ) AS NDML -- End of nested DML

END

写法2：用JOIN关联拆分后的表 #

当Guid列表较长时，这种写法性能更优：

CREATE PROCEDURE spSavedPlaces
@GuidList NVARCHAR(MAX)
AS
BEGIN

  SET NOCOUNT ON;

  INSERT INTO OutputTable(
    SavedPlaceId,
    [Name],
    IsOpen
  )
  SELECT
    SavedPlaceId,
    [Name],
    IsOpen
  FROM (UPDATE P
          SET
            IsOpen = 1
        OUTPUT
          INSERTED.SavedPlaceId,
          INSERTED.[Name],
          INSERTED.IsOpen
       FROM SavedPlace AS P
       INNER JOIN dbo.SplitStrings_XML(@GuidList, ',') AS Splitter
           ON P.SavedPlaceId = CAST(Splitter.Item AS UNIQUEIDENTIFIER)
  ) AS NDML -- End of nested DML

END

核心注意点 #

• 拆分函数必须在UPDATE的FROM/WHERE部分调用，外层SELECT只负责接收更新后的输出结果，无法控制要更新的行。
• 必须把拆分得到的Item转换为UNIQUEIDENTIFIER类型，否则会因类型不匹配导致筛选失效。
• 调用函数时要传入正确的分隔符（比如你的Guid列表是用逗号分隔的，就传','）。

内容的提问来源于stack exchange，提问作者CodingIsFunYouShouldTryIt