如何获取执行失败的UPDATE语句的OUTPUT结果？

我需要把十六进制ID列表转成十进制，为了避免用CURSOR写了T-SQL代码，但最后一个ID是非法十六进制值，导致UPDATE执行失败，连已经成功处理的OUTPUT结果也拿不到。想知道怎么在UPDATE失败时还能获取已经成功转换的输出。

原代码如下：

-- Test IDs, last one has an error
DECLARE @hexIDs AS VARCHAR(255) = '00000AAAAAA,00000BBBBBB,00000CCCCCC,X0000DDDDDD';

-- Table which holds hex IDs and converted IDs (necessary for feedback if something fails)
DECLARE @ids AS TABLE(IDHex VARCHAR(11), ID BIGINT);

-- Table to track updated ids
DECLARE @inserted AS TABLE(ID BIGINT);

BEGIN TRY
    INSERT INTO @ids (IDHex) SELECT * FROM string_split(@hexIDs, ',');
    
    UPDATE @ids SET ID = CAST(CONVERT(VARBINARY, '0' + IDHex, 2) AS BIGINT)
      OUTPUT inserted.ID INTO @inserted;
    
    SELECT * FROM @ids;
    SELECT * FROM @inserted;
END TRY
BEGIN CATCH
    SELECT  
      ERROR_NUMBER() AS ErrorNumber  
      ,ERROR_SEVERITY() AS ErrorSeverity  
      ,ERROR_STATE() AS ErrorState  
      ,ERROR_PROCEDURE() AS ErrorProcedure  
      ,ERROR_LINE() AS ErrorLine  
      ,ERROR_MESSAGE() AS ErrorMessage;
    SELECT * FROM @ids;
    SELECT * FROM @inserted;
END CATCH

原因分析 #

T-SQL里的UPDATE是原子操作，只要有一行转换失败，整个UPDATE会被回滚，所以@inserted表不会有任何数据，之前成功转换的行也会被撤销。

解决方案 #

方案1：用TRY_CONVERT实现容错批量转换（推荐） #

利用TRY_CONVERT函数的容错特性，它在转换失败时会返回NULL而不是抛出错误，这样整个UPDATE不会中断，同时还能标记出转换失败的行。

-- Test IDs, last one has an error
DECLARE @hexIDs AS VARCHAR(255) = '00000AAAAAA,00000BBBBBB,00000CCCCCC,X0000DDDDDD';

-- 存储原始ID、转换结果和状态
DECLARE @ids AS TABLE(
    IDHex VARCHAR(11), 
    ID BIGINT,
    ConversionStatus VARCHAR(100)
);

INSERT INTO @ids (IDHex) SELECT * FROM string_split(@hexIDs, ',');

-- 批量转换，失败的行标记状态
UPDATE @ids 
SET 
    ID = TRY_CAST(TRY_CONVERT(VARBINARY, '0' + IDHex, 2) AS BIGINT),
    ConversionStatus = CASE 
        WHEN TRY_CONVERT(VARBINARY, '0' + IDHex, 2) IS NULL THEN '转换失败：非法十六进制格式'
        ELSE '转换成功'
    END;

-- 查看所有行的转换结果
SELECT * FROM @ids;

-- 单独提取成功转换的十进制ID
SELECT ID FROM @ids WHERE ConversionStatus = '转换成功';

方案2：逐行处理（细粒度错误记录） #

如果需要记录每个错误的具体信息，可以用WHILE循环逐行处理，捕获单条行的错误，这样即使某行失败，前面的成功行依然保留。

-- Test IDs, last one has an error
DECLARE @hexIDs AS VARCHAR(255) = '00000AAAAAA,00000BBBBBB,00000CCCCCC,X0000DDDDDD';

-- 存储原始ID、转换结果、状态和行号（用于逐行定位）
DECLARE @ids AS TABLE(
    IDHex VARCHAR(11), 
    ID BIGINT,
    ConversionStatus VARCHAR(200),
    RowNum INT IDENTITY(1,1) PRIMARY KEY
);
-- 存储成功转换的ID
DECLARE @inserted AS TABLE(ID BIGINT);

INSERT INTO @ids (IDHex) SELECT * FROM string_split(@hexIDs, ',');

DECLARE @currentRow INT = 1;
DECLARE @maxRow INT = (SELECT MAX(RowNum) FROM @ids);
DECLARE @currentHex VARCHAR(11);

WHILE @currentRow <= @maxRow
BEGIN
    SELECT @currentHex = IDHex FROM @ids WHERE RowNum = @currentRow;
    
    BEGIN TRY
        -- 转换当前行
        UPDATE @ids 
        SET 
            ID = CAST(CONVERT(VARBINARY, '0' + @currentHex, 2) AS BIGINT),
            ConversionStatus = '转换成功'
        WHERE RowNum = @currentRow;
        
        -- 插入成功结果到@inserted表
        INSERT INTO @inserted SELECT ID FROM @ids WHERE RowNum = @currentRow;
    END TRY
    BEGIN CATCH
        -- 记录错误信息
        UPDATE @ids 
        SET ConversionStatus = '转换失败：' + ERROR_MESSAGE()
        WHERE RowNum = @currentRow;
    END CATCH
    
    SET @currentRow = @currentRow + 1;
END

-- 查看所有行的转换状态
SELECT * FROM @ids;
-- 查看成功转换的ID列表
SELECT * FROM @inserted;

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