SQL存储过程WHERE子句逻辑错误排查及性能优化咨询

你写的条件判断完全搞反了@UseName、@UseEmail的分支逻辑：

• 需求是如果参数不为空（@UseName=1），就执行LIKE匹配；如果参数为空（@UseName=0），就跳过这个条件不限制
• 现有代码的逻辑是(@UseName = 1) OR ([Name] LIKE '%' + @Name + '%')，和需求完全相反：
  1. 当参数为空@UseName=0时，表达式变成0 OR [Name] LIKE '%' + NULL + '%'，LIKE NULL的结果是UNKNOWN，整个WHERE条件不成立，所以前4次两个参数都为空的查询返回0条。
  2. 当参数不为空@UseName=1时，表达式变成1 OR 任意判断，恒为真，等于这个条件完全没生效，所以第5次查询两个参数都不为空时，WHERE条件变成真 AND 真，返回所有3条数据。

修正后的WHERE逻辑如下：

IF (@Condition = 0)
    SELECT [Id], [Name], [Email]
    FROM [User]
    WHERE
        ((@UseName = 0) OR ([Name] LIKE '%' + @Name + '%'))
        AND
        ((@UseEmail = 0) OR ([Email] LIKE '%' + @Email + '%'))
ELSE
    SELECT [Id], [Name], [Email]
    FROM [User]
    WHERE
        ((@UseName = 0) OR ([Name] LIKE '%' + @Name + '%'))
        OR
        ((@UseEmail = 0) OR ([Email] LIKE '%' + @Email + '%'))
-- 可选：加OPTION (RECOMPILE)解决参数嗅探问题

现有写法的性能问题 #

你当前的实现方式不是性能最优的：

• 这种OR拼接可选条件的写法，会导致SQL Server生成的执行计划无法适配所有参数场景，容易出现参数嗅探，大表下大概率走全表扫描，性能很差。
• 你用了前后都带%的模糊查询，无法用到普通的B树索引，文本匹配效率极低，数据量越大性能衰减越明显。

CURSOR完全不适用该场景 #

游标是逐行处理数据的语法，专门用于需要逐行运算的场景，你这个是批量检索数据集的需求，用游标只会让性能下降几十到上百倍，完全不要考虑。

优化建议 #

• 如果必须用前后%的模糊查询，优先给Name、Email字段加全文索引，用CONTAINS、FREETEXT语法做检索，性能比LIKE高几个量级，是大表文本检索的最优方案。
• 如果你不想用全文索引，可选参数查询优先用参数化动态SQL实现，根据传的参数拼接需要的WHERE条件，用sp_executesql执行，既可以避免SQL注入，又能让每个参数场景生成最优的执行计划，不会有OR条件导致的全表扫描问题，示例写法如下：

CREATE OR ALTER PROCEDURE SpUserSearch
    @Condition BIT = 0, -- AND=0, OR=1.
    @Name NVARCHAR(100) = NULL,
    @Email NVARCHAR(100) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Sql NVARCHAR(MAX) = N'SELECT [Id], [Name], [Email] FROM [User] WHERE 1=1 ',
            @Params NVARCHAR(MAX) = N'@Name NVARCHAR(100), @Email NVARCHAR(100)',
            @ConcatSymbol NVARCHAR(3) = CASE WHEN @Condition = 0 THEN N'AND' ELSE N'OR' END

    IF @Name IS NOT NULL AND LEN(@Name) > 0
        SET @Sql += @ConcatSymbol + N' [Name] LIKE ''%'' + @Name + ''%'' '
    IF @Email IS NOT NULL AND LEN(@Email) > 0
        SET @Sql += @ConcatSymbol + N' [Email] LIKE ''%'' + @Email + ''%'' '

    EXEC sp_executesql @Sql, @Params, @Name = @Name, @Email = @Email
    RETURN @@ROWCOUNT
END

• 如果存储过程调用频率不高，也可以在原修正后的查询末尾加OPTION (RECOMPILE)，让SQL Server每次根据实际参数重编译执行计划，虽然有少量编译开销，但比全表扫描的开销低很多。

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