存储过程搜索功能失效：传递不同参数却返回相同结果

核心问题：存储过程与表同名 #

你的存储过程命名为[dbo].[users]，和目标查询表dbo.users完全重名。SQL Server在解析对象名称时，会优先选择存储过程而非表，导致CTE里的SELECT * FROM dbo.users实际上是在递归调用这个存储过程，而非查询表数据——这就是为什么无论传递什么搜索参数，返回结果都一致的根本原因。

其他次要问题及优化点 #

除了同名问题，还有几个需要修正的细节：

• ORDER BY逻辑不完整：当前仅处理了@SortColumn = 1的情况，其他列排序时没有明确规则，会导致分页结果不稳定。
• 笛卡尔积连接：SELECT * FROM CTE_Result, CTE_Count是隐式笛卡尔积，应该用CROSS JOIN明确关联，避免语义歧义。
• 搜索条件冗余：@SEARCH != ''的判断重复，可以简化逻辑。
• 参数大小写不一致：WHERE子句中混用@SEARCH和@search，虽然SQL Server默认不区分大小写，但统一写法更规范。

修复后的完整代码 #

CREATE OR ALTER PROCEDURE [dbo].[GetUsers] -- 重命名存储过程，避免与表同名
    @SEARCH VARCHAR(100)='',  -- 全局过滤参数
    @PageNumber INT,
    @PageSize INT,
    @SortOrder VARCHAR(10),
    @SortColumn INT
AS
BEGIN
    SET NOCOUNT ON

    BEGIN TRY
        DECLARE @RecordFrom INT;
        SET @RecordFrom = (@PageNumber-1) * @PageSize;
 
        ;WITH CTE_Result (id, name, email, department) AS
        (
            SELECT id, name, email, department -- 明确指定列，避免SELECT *的潜在问题
            FROM dbo.users
            WHERE @SEARCH = '' 
               OR name LIKE CONCAT('%', @SEARCH, '%') 
               OR email LIKE CONCAT('%', @SEARCH, '%')
        ), 
        CTE_Count AS 
        (
            SELECT COUNT(id) AS TotalRecords 
            FROM CTE_Result
        )
        SELECT r.*, c.TotalRecords
        FROM CTE_Result r
        CROSS JOIN CTE_Count c -- 明确交叉连接，获取总记录数
        ORDER BY
            CASE WHEN @SortColumn = 1 AND @SortOrder = 'asc' THEN id END ASC,
            CASE WHEN @SortColumn = 1 AND @SortOrder = 'desc' THEN id END DESC,
            CASE WHEN @SortColumn = 2 AND @SortOrder = 'asc' THEN name END ASC,
            CASE WHEN @SortColumn = 2 AND @SortOrder = 'desc' THEN name END DESC,
            CASE WHEN @SortColumn = 3 AND @SortOrder = 'asc' THEN email END ASC,
            CASE WHEN @SortColumn = 3 AND @SortOrder = 'desc' THEN email END DESC,
            CASE WHEN @SortColumn = 4 AND @SortOrder = 'asc' THEN department END ASC,
            CASE WHEN @SortColumn = 4 AND @SortOrder = 'desc' THEN department END DESC
            OFFSET @RecordFrom ROWS 
            FETCH NEXT @PageSize ROWS ONLY
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH

    SET NOCOUNT OFF
END

关键修复说明 #

1. 重命名存储过程：将存储过程改为dbo.GetUsers，彻底避免与表名冲突，确保CTE中正确查询目标表。
2. 明确指定查询列：替换SELECT *为具体列名，避免CTE列定义与实际查询列不匹配的问题。
3. 完善ORDER BY逻辑：补充了所有列（id、name、email、department）的排序规则，确保分页结果稳定。
4. 简化搜索条件：去除冗余的@SEARCH != ''判断，逻辑更简洁清晰。
5. 使用CONCAT拼接模糊查询：避免+拼接时如果@SEARCH为NULL导致整个表达式为NULL的问题。

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