使用STUFF+XML与OPENQUERY时，过滤后仍触发类型转换错误？

我平时用STUFF结合XML实现多行值合并为单行（比如按Product合并Category），现在要在跨链接服务器查询里复用这个逻辑，为了优化性能用了OPENQUERY。但其中一张本地表bad_table的ID字段是varchar类型，里面有大量非数值、空字符串之类的无效数据。虽然我用CTE、子查询预先过滤了无效ID，但执行查询时还是触发Conversion failed when converting the varchar value 'ABC' to data type int错误。

当前查询代码：

with src as (
select * from openquery(
[LinkedServer], 'select * from [LinkedServer].[a].[b].[good_table] where ID in (
select ID from [LocalServer].[a].[b].[bad_table]
where
ID not like ''%[A-Z]%'' and ID not like ''%.%'' and ID not like ''''
)')
)
select distinct
Dates = STUFF((
    select distinct ' / ' + bt2.aDate
    from (
            select distinct 
            convert(nvarchar(MAX), bt1.[date], 23) as 'aDate'
            , bt1.ID
            from bad_table bt1
            where
            bt1.ID not like '%[A-Z]%' and bt1.ID not like '%.%' and bt1.ID not like ''
        ) bt2
        where
        bt2.id = s1.id
        for xml path('')),1,3,'')
, s1.ID
, s1.*
from src s1

现在只有手动指定排除特定无效值（比如bt2.id = s1.id and bt2.ID not in ('.', 'abc','hhh','WWQS')）能临时生效，但这不是合理方案，求问题根源和正确解决方法。

问题根源 #

SQL Server的查询优化器可能会调整过滤条件的执行顺序，导致你写的过滤逻辑在类型转换之后才执行：

• good_table的ID字段是int类型，而bad_table的ID是varchar类型
• 跨服务器查询时，优化器可能会先尝试把bad_table的varchar类型ID和good_table的int类型ID做关联匹配，自动触发隐式类型转换（把varchar转成int）
• 你的过滤条件虽然写了，但优化器没按预期顺序执行，导致无效的非数值字符串先被拿来转int，直接报错

另外，OPENQUERY内部直接引用本地表的子查询，也可能让优化器无法正确评估过滤逻辑，提前触发转换。

正确解决方法 #

方法1：提前提取有效ID到本地临时表，再传给OPENQUERY #

先在本地把bad_table里的有效ID筛选出来存到临时表，再让OPENQUERY用这个临时表做关联，彻底避免跨服务器时的隐式转换问题：

-- 第一步：提取有效ID到本地临时表，加严谨的数值判断
SELECT ID INTO #ValidIDs
FROM [LocalServer].[a].[b].[bad_table]
WHERE 
    ID <> ''
    AND ID NOT LIKE '%[A-Z]%' 
    AND ID NOT LIKE '%.%' 
    AND ISNUMERIC(ID + '.0e0') = 1; -- 避免漏过'123a'这类伪数值

-- 第二步：把有效ID拼接成字符串传给OPENQUERY
DECLARE @validIDs NVARCHAR(MAX);
SELECT @validIDs = STRING_AGG(QUOTENAME(ID, ''''), ',') FROM #ValidIDs;

WITH src AS (
    SELECT * FROM OPENQUERY(
        [LinkedServer], 
        'SELECT * FROM [a].[b].[good_table] WHERE ID IN (' + @validIDs + ')'
    )
)
SELECT DISTINCT
    Dates = STUFF((
        SELECT DISTINCT ' / ' + CONVERT(NVARCHAR(MAX), bt1.[date], 23)
        FROM [LocalServer].[a].[b].[bad_table] bt1
        WHERE 
            bt1.ID IN (SELECT ID FROM #ValidIDs)
            AND bt1.ID = s1.ID
        FOR XML PATH('')), 1, 3, ''),
    s1.ID,
    s1.*
FROM src s1;

DROP TABLE #ValidIDs;

方法2：用TRY_CAST/TRY_CONVERT做安全转换（SQL Server 2012+） #

如果你的SQL Server版本是2012及以上，用TRY_CAST来安全转换——转换失败会返回NULL，不会触发报错，同时保留过滤逻辑：

WITH src AS (
    SELECT * FROM OPENQUERY(
        [LinkedServer], 
        'SELECT * FROM [a].[b].[good_table] g
        INNER JOIN [LocalServer].[a].[b].[bad_table] b
            ON TRY_CAST(b.ID AS INT) = g.ID
        WHERE 
            b.ID <> ''
            AND b.ID NOT LIKE ''%[A-Z]%'' 
            AND b.ID NOT LIKE ''%.%'' 
        '
    )
)
SELECT DISTINCT
    Dates = STUFF((
        SELECT DISTINCT ' / ' + CONVERT(NVARCHAR(MAX), bt1.[date], 23)
        FROM [LocalServer].[a].[b].[bad_table] bt1
        WHERE 
            TRY_CAST(bt1.ID AS INT) = s1.ID
            AND bt1.ID <> ''
            AND bt1.ID NOT LIKE '%[A-Z]%' 
            AND bt1.ID NOT LIKE '%.%' 
        FOR XML PATH('')), 1, 3, ''),
    s1.ID,
    s1.*
FROM src s1;

方法3：在关联时先做过滤再显式转换 #

强制让过滤逻辑先执行，再做类型转换，避免优化器打乱顺序：

WITH src AS (
    SELECT * FROM OPENQUERY(
        [LinkedServer], 
        'SELECT * FROM [a].[b].[good_table] WHERE ID IN (
            SELECT CAST(ID AS INT) 
            FROM [LocalServer].[a].[b].[bad_table]
            WHERE 
                ID <> ''
                AND ID NOT LIKE ''%[A-Z]%'' 
                AND ID NOT LIKE ''%.%'' 
                AND ISNUMERIC(ID + ''.0e0'') = 1
        )'
    )
)
SELECT DISTINCT
    Dates = STUFF((
        SELECT DISTINCT ' / ' + CONVERT(NVARCHAR(MAX), bt1.[date], 23)
        FROM [LocalServer].[a].[b].[bad_table] bt1
        WHERE 
            ISNUMERIC(bt1.ID + '.0e0') = 1
            AND bt1.ID <> ''
            AND bt1.ID NOT LIKE '%[A-Z]%' 
            AND bt1.ID NOT LIKE '%.%' 
            AND CAST(bt1.ID AS INT) = s1.ID
        FOR XML PATH('')), 1, 3, ''),
    s1.ID,
    s1.*
FROM src s1;

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