SQL Server筛选查询存储过程逻辑问题排查咨询

问题描述 #

编写用于多表筛选搜索的SQL Server存储过程，通过参数传入多值列表并存入临时表，现有「Services」「Equipment」「Zones」三个筛选条件，用户可选择一个或多个条件，每个条件可包含多个选项。当前逻辑存在问题：

• 无法正确处理仅应用部分筛选条件的场景
• WHERE子句条件执行错误，例如执行EXEC SearchFilterProvider NULL, NULL, 'Norte|Bajio'时，本该仅返回同时包含Norte和Bajio区域的供应商R2，实际却返回了仅包含Bajio的R1和包含双区域的R2。

原存储过程代码 #

ALTER PROCEDURE [dbo].[SearchFilterProvider]
(@SearchService nvarchar(max)=NULL,@SearchEquipment nvarchar(max)=NULL, @SearchZone nvarchar(max)=NULL)
AS
BEGIN
    BEGIN TRAN
        BEGIN TRY
            CREATE TABLE #Services (TipoServicio nvarchar(35));
            INSERT INTO #Services (TipoServicio)
                SELECT VALUE FROM string_split(@SearchService,'|');
            CREATE TABLE #Equipments (TipoEquipo nvarchar(35));
            INSERT INTO #Equipments (TipoEquipo)
                SELECT VALUE FROM string_split(@SearchEquipment,'|');
            CREATE TABLE #Zones (NombreZona nvarchar(35));
            INSERT INTO #Zones (NombreZona)
                SELECT VALUE FROM string_split(@SearchZone,'|');
            
            SELECT P.RazonSocial as 'Razon Social Proveedor', P.TipoProveedor as Tipo,
            (
                SELECT STRING_AGG(Z.NombreZona,';')
                FROM Rel_ProvZona pZ
                INNER JOIN Zona Z on pZ.idZona= Z.idZona
                WHERE pZ.idProveedor=P.idProveedor
            )AS Zonas,
            P.Estatus,P.Ciudad,P.Estado,P.Pais,P.Comentarios,U.Nombre+' ' +U.Apellido AS Usuario
            FROM Proveedor P
            INNER JOIN Usuario U ON P.Despachador=U.idUsuario
            INNER JOIN Rel_ProvServicio pS ON P.idProveedor= pS.idProveedor
            INNER JOIN Servicio S ON pS.idServicio=S.idServicio
            INNER JOIN Rel_ProvEquipo pE ON P.idProveedor= pE.idProveedor
            INNER JOIN Equipo E ON pE.idEquipo=E.idEquipo
            INNER JOIN Rel_ProvZona pZ ON P.idProveedor= pZ.idProveedor
            INNER JOIN Zona Z ON pZ.idZona=Z.idZona
            WHERE  S.TipoServicio IN (SELECT TipoServicio FROM #Services) OR  Z.NombreZona IN (SELECT NombreZona FROM #Zones) OR E.TipoEquipo IN (SELECT TipoEquipo FROM #Equipments)
          GROUP BY P.idProveedor,P.RazonSocial,P.TipoProveedor, P.Estatus,P.Ciudad,P.Estado,P.Pais,P.Comentarios,(U.Nombre+' '+U.Apellido)
            COMMIT TRAN
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT>0
                BEGIN
                    ROLLBACK TRAN
                END
        END CATCH
END

问题分析 #

1. 筛选条件逻辑错误：使用OR连接多个筛选条件，导致只要满足任意一个条件的供应商都会被返回，而正确逻辑应该是用户选择的多个条件需同时满足（用AND连接）。
2. 单条件多选项匹配错误：使用IN实现的是“包含任意一个选项”，但用户需求是单条件下选择多个选项时，供应商需包含所有选中项。
3. 空参数处理缺失：当参数为NULL时，对应的临时表为空，此时IN条件等价于FALSE，会影响整体逻辑；正确处理应为空参数对应的筛选条件直接跳过，不参与过滤。
4. 关联表JOIN方式不当：使用INNER JOIN会过滤掉没有服务、设备或区域关联的供应商，若需保留此类供应商应改为LEFT JOIN（结合筛选条件调整）。

修正后的存储过程代码 #

ALTER PROCEDURE [dbo].[SearchFilterProvider]
(@SearchService nvarchar(max)=NULL,@SearchEquipment nvarchar(max)=NULL, @SearchZone nvarchar(max)=NULL)
AS
BEGIN
    BEGIN TRAN
        BEGIN TRY
            -- 标记参数是否有效
            DECLARE @HasService BIT = CASE WHEN @SearchService IS NOT NULL THEN 1 ELSE 0 END;
            DECLARE @HasEquipment BIT = CASE WHEN @SearchEquipment IS NOT NULL THEN 1 ELSE 0 END;
            DECLARE @HasZone BIT = CASE WHEN @SearchZone IS NOT NULL THEN 1 ELSE 0 END;

            CREATE TABLE #Services (TipoServicio nvarchar(35));
            IF @HasService = 1
                INSERT INTO #Services (TipoServicio)
                    SELECT VALUE FROM string_split(@SearchService,'|') WHERE VALUE IS NOT NULL AND VALUE <> '';

            CREATE TABLE #Equipments (TipoEquipo nvarchar(35));
            IF @HasEquipment = 1
                INSERT INTO #Equipments (TipoEquipo)
                    SELECT VALUE FROM string_split(@SearchEquipment,'|') WHERE VALUE IS NOT NULL AND VALUE <> '';

            CREATE TABLE #Zones (NombreZona nvarchar(35));
            IF @HasZone = 1
                INSERT INTO #Zones (NombreZona)
                    SELECT VALUE FROM string_split(@SearchZone,'|') WHERE VALUE IS NOT NULL AND VALUE <> '';

            SELECT 
                P.RazonSocial AS 'Razon Social Proveedor',
                P.TipoProveedor AS Tipo,
                (
                    SELECT STRING_AGG(Z.NombreZona,';')
                    FROM Rel_ProvZona pZ
                    INNER JOIN Zona Z ON pZ.idZona = Z.idZona
                    WHERE pZ.idProveedor = P.idProveedor
                ) AS Zonas,
                P.Estatus,
                P.Ciudad,
                P.Estado,
                P.Pais,
                P.Comentarios,
                U.Nombre + ' ' + U.Apellido AS Usuario
            FROM Proveedor P
            INNER JOIN Usuario U ON P.Despachador = U.idUsuario
            LEFT JOIN Rel_ProvServicio pS ON P.idProveedor = pS.idProveedor
            LEFT JOIN Servicio S ON pS.idServicio = S.idServicio
            LEFT JOIN Rel_ProvEquipo pE ON P.idProveedor = pE.idProveedor
            LEFT JOIN Equipo E ON pE.idEquipo = E.idEquipo
            LEFT JOIN Rel_ProvZona pZ ON P.idProveedor = pZ.idProveedor
            LEFT JOIN Zona Z ON pZ.idZona = Z.idZona
            WHERE 
                -- 服务筛选：无参数则跳过，有参数则需匹配所有选中服务
                (@HasService = 0 OR EXISTS (
                    SELECT 1
                    FROM Rel_ProvServicio ps
                    JOIN Servicio s ON ps.idServicio = s.idServicio
                    WHERE ps.idProveedor = P.idProveedor
                    AND s.TipoServicio IN (SELECT TipoServicio FROM #Services)
                    GROUP BY ps.idProveedor
                    HAVING COUNT(DISTINCT s.TipoServicio) = (SELECT COUNT(*) FROM #Services)
                ))
                -- 设备筛选：无参数则跳过，有参数则需匹配所有选中设备
                AND (@HasEquipment = 0 OR EXISTS (
                    SELECT 1
                    FROM Rel_ProvEquipo pe
                    JOIN Equipo e ON pe.idEquipo = e.idEquipo
                    WHERE pe.idProveedor = P.idProveedor
                    AND e.TipoEquipo IN (SELECT TipoEquipo FROM #Equipments)
                    GROUP BY pe.idProveedor
                    HAVING COUNT(DISTINCT e.TipoEquipo) = (SELECT COUNT(*) FROM #Equipments)
                ))
                -- 区域筛选：无参数则跳过，有参数则需匹配所有选中区域
                AND (@HasZone = 0 OR EXISTS (
                    SELECT 1
                    FROM Rel_ProvZona pz
                    JOIN Zona z ON pz.idZona = z.idZona
                    WHERE pz.idProveedor = P.idProveedor
                    AND z.NombreZona IN (SELECT NombreZona FROM #Zones)
                    GROUP BY pz.idProveedor
                    HAVING COUNT(DISTINCT z.NombreZona) = (SELECT COUNT(*) FROM #Zones)
                ))
            GROUP BY 
                P.idProveedor,
                P.RazonSocial,
                P.TipoProveedor,
                P.Estatus,
                P.Ciudad,
                P.Estado,
                P.Pais,
                P.Comentarios,
                U.Nombre + ' ' + U.Apellido
            COMMIT TRAN
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
                ROLLBACK TRAN;
            -- 可添加错误日志记录
            THROW;
        END CATCH
END

关键修正点说明 #

1. 空参数处理：通过@HasService等标记位判断参数是否有效，仅当参数非空时才插入临时表数据，筛选时直接跳过空参数对应的条件。
2. 多选项全匹配：使用EXISTS结合GROUP BY + HAVING COUNT，确保供应商关联的项数量等于选中的选项数量，实现“包含所有选中项”的逻辑。
3. 筛选条件逻辑：用AND连接各个筛选条件，确保用户选择的多个条件需同时满足。
4. 关联表优化：改为LEFT JOIN避免过滤掉无关联数据的供应商，同时在EXISTS子查询中重新关联对应表，保证筛选逻辑准确。

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