SQL Server 2019：如何按变量值控制交集查询的TVF调用

一、让INTERSECT返回正确结果的参数处理 #

你当前的核心问题是：当@att1或@att2为NULL/0时，对应的表值函数（TVF）返回空集，导致INTERSECT最终结果为空。要解决这个问题，只需让TVF在传入NULL/0这类无效参数时，返回主表record的全部recordID，这样INTERSECT的逻辑就能自动适配不同参数情况：

• 若@att1为NULL/0，searchAtt1返回所有recordID，INTERSECT searchAtt2(@att2)等价于直接取searchAtt2的结果
• 若两个参数都是有效值，就取两个TVF结果的交集
• 若@att2为NULL/0，同理等价于取searchAtt1的结果

修改TVF的示例（以searchAtt1为例）：

CREATE FUNCTION searchAtt1(@att1 INT)
RETURNS TABLE
AS
RETURN
(
    -- 参数有效时，返回关联的recordID
    SELECT recordID 
    FROM record_att1_link -- 你的联结表
    WHERE @att1 IS NOT NULL AND @att1 != 0 AND att1 = @att1
    -- 参数无效时，返回主表所有recordID
    UNION ALL
    SELECT recordID 
    FROM record
    WHERE @att1 IS NULL OR @att1 = 0
);

同理修改searchAtt2后，原查询的INTERSECT逻辑就能正常工作。

二、根据变量值跳过TVF调用 #

如果无法修改现有TVF，可以通过条件分支或动态SQL实现按需调用：

方法1：IF ELSE分支处理（直观易维护） #

直接根据变量值分场景编写查询：

DECLARE @att1 INT = NULL;
DECLARE @att2 INT = NULL;

IF @att1 IS NULL
BEGIN
    -- 仅调用searchAtt2
    SELECT recordID, title, trackingNumber
    FROM record
    WHERE recordID IN (SELECT * FROM searchAtt2(@att2));
END
ELSE IF @att2 IS NULL
BEGIN
    -- 仅调用searchAtt1
    SELECT recordID, title, trackingNumber
    FROM record
    WHERE recordID IN (SELECT * FROM searchAtt1(@att1));
END
ELSE
BEGIN
    -- 两个TVF都调用，取交集
    SELECT recordID, title, trackingNumber
    FROM record
    WHERE recordID IN (
        SELECT * FROM searchAtt1(@att1)
        INTERSECT
        SELECT * FROM searchAtt2(@att2)
    );
END

方法2：动态SQL拼接（适合多参数场景） #

如果后续参数增多，分支逻辑太繁琐，可通过动态SQL按需拼接查询条件：

DECLARE @att1 INT = NULL;
DECLARE @att2 INT = NULL;
DECLARE @sql NVARCHAR(MAX);
DECLARE @filter NVARCHAR(MAX) = '';

-- 拼接att1的过滤条件
IF @att1 IS NOT NULL AND @att1 != 0
BEGIN
    SET @filter += 'recordID IN (SELECT * FROM searchAtt1(@att1))';
END

-- 拼接att2的过滤条件
IF @att2 IS NOT NULL AND @att2 != 0
BEGIN
    SET @filter += CASE WHEN @filter != '' THEN ' INTERSECT ' ELSE '' END 
                   + 'recordID IN (SELECT * FROM searchAtt2(@att2))';
END

-- 组装完整SQL
SET @sql = N'
SELECT recordID, title, trackingNumber
FROM record' 
+ CASE WHEN @filter != '' THEN N' WHERE ' + @filter ELSE N'' END;

-- 执行动态SQL并传入参数
EXEC sp_executesql @sql, N'@att1 INT, @att2 INT', @att1 = @att1, @att2 = @att2;

总结 #

• 能修改TVF的话，优先给TVF添加“无效参数返回全量recordID”的逻辑，原查询无需改动即可正常运行
• 不能修改TVF的话，用IF分支或动态SQL按需调用，避免无效的INTERSECT导致空结果

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