相同输入下SQL Server存储过程执行结果不一致求助

这种相同查询条件下返回记录数逐步增加的异常，核心原因大概率是数据读取的一致性问题，结合你提到的临时表/表变量替换后问题依旧，可从以下方向排查解决：

可能的触发点 #

• 隔离级别过低：若数据库默认隔离级别为READ UNCOMMITTED，会读取到未提交的事务数据，当有其他事务分批插入/更新Locations表时，就会出现每次查询结果逐步增加的情况。
• 低效查询写法导致的执行计划异常：所有条件都用OR @参数 IS NULL的模式，会让SQL Server难以生成稳定的执行计划，并发场景下容易出现数据读取不完整。
• 无事务包裹的分步操作：原代码中临时表插入、计数查询、分页查询是分步执行的，若期间有表数据变更，会导致多次读取结果不一致。

具体解决方案 #

1. 强制设置事务隔离级别 #

在存储过程开头添加隔离级别配置，确保读取已提交的一致数据：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

该级别会锁定读取的行，避免同一事务内多次读取出现数据变化。如果需要更宽松的一致性，也可开启数据库的行版本控制：

ALTER DATABASE YourDatabaseName SET READ_COMMITTED_SNAPSHOT ON;

开启后READ COMMITTED隔离级别会读取事务开始时的数据快照，避免锁等待同时保证一致性。

2. 优化查询条件写法，避免OR @参数 IS NULL #

这种写法会导致索引失效，且执行计划不稳定。改用动态SQL拼接非空条件，同时用sp_executesql参数化执行避免注入：

CREATE PROCEDURE [dbo].[Location_SearchAll]
    @LocationName varchar(100) = null,
    @LocationId varchar(10) = null,
    @Address varchar(40) = null,
    @City varchar(35) = null,
    @StateProvince varchar(2) = null,
    @PostalCode varchar(9) = null,
    @CountryCode varchar(2) = null,
    @PhoneNumber varchar(20) = null,
    @ServiceLevel varchar(35) = null,
    @Page int = 1,
    @ItemsPerPage int = 100
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    DECLARE @sql NVARCHAR(MAX), @countSql NVARCHAR(MAX);
    DECLARE @where NVARCHAR(MAX) = N'';

    -- 拼接非空查询条件
    IF @LocationName IS NOT NULL
        SET @where += N' AND l.LocationName LIKE ''%'' + @LocationName + ''%''';
    IF @LocationId IS NOT NULL
        SET @where += N' AND l.LocationId LIKE ''%'' + @LocationId + ''%''';
    IF @Address IS NOT NULL
        SET @where += N' AND l.AddressLine1 LIKE ''%'' + @Address + ''%''';
    IF @City IS NOT NULL
        SET @where += N' AND l.City LIKE ''%'' + @City + ''%''';
    IF @StateProvince IS NOT NULL
        SET @where += N' AND l.StateProvince LIKE ''%'' + @StateProvince + ''%''';
    IF @PostalCode IS NOT NULL
        SET @where += N' AND l.PostalCode LIKE ''%'' + @PostalCode + ''%''';
    IF @CountryCode IS NOT NULL
        SET @where += N' AND l.CountryCode LIKE ''%'' + @CountryCode + ''%''';
    IF @PhoneNumber IS NOT NULL
        SET @where += N' AND l.Phone LIKE ''%'' + @PhoneNumber + ''%''';
    IF @ServiceLevel IS NOT NULL
        SET @where += N' AND l.ServiceLevel LIKE ''%'' + @ServiceLevel + ''%''';

    -- 移除开头多余的AND
    IF LEN(@where) > 0
        SET @where = N' WHERE ' + STUFF(@where, 1, 5, N'');

    -- 拼接总记录数查询
    SET @countSql = N'
        SELECT COUNT(*) AS TotalCount, 
               CEILING(COUNT(*) / CAST(@ItemsPerPage AS FLOAT)) AS TotalPages
        FROM Locations l' + @where;

    -- 拼接分页查询
    SET @sql = N'
        SELECT *
        FROM Locations l' + @where + N'
        ORDER BY Id
        OFFSET (@Page - 1) * @ItemsPerPage ROWS
        FETCH NEXT @ItemsPerPage ROWS ONLY;';

    -- 执行总记录数查询
    EXEC sp_executesql @countSql, 
        N'@LocationName varchar(100), @LocationId varchar(10), @Address varchar(40), 
          @City varchar(35), @StateProvince varchar(2), @PostalCode varchar(9), 
          @CountryCode varchar(2), @PhoneNumber varchar(20), @ServiceLevel varchar(35), 
          @ItemsPerPage int',
        @LocationName, @LocationId, @Address, @City, @StateProvince, @PostalCode,
        @CountryCode, @PhoneNumber, @ServiceLevel, @ItemsPerPage;

    -- 执行分页查询
    EXEC sp_executesql @sql, 
        N'@LocationName varchar(100), @LocationId varchar(10), @Address varchar(40), 
          @City varchar(35), @StateProvince varchar(2), @PostalCode varchar(9), 
          @CountryCode varchar(2), @PhoneNumber varchar(20), @ServiceLevel varchar(35), 
          @Page int, @ItemsPerPage int',
        @LocationName, @LocationId, @Address, @City, @StateProvince, @PostalCode,
        @CountryCode, @PhoneNumber, @ServiceLevel, @Page, @ItemsPerPage;
END
GO

3. 用显式事务包裹临时表操作 #

如果坚持使用临时表，将插入、计数、分页查询放入同一个事务，保证数据读取的一致性：

CREATE PROCEDURE [dbo].[Location_SearchAll]
    @LocationName varchar(100) = null,
    @LocationId varchar(10) = null,
    @Address varchar(40) = null,
    @City varchar(35) = null,
    @StateProvince varchar(2) = null,
    @PostalCode varchar(9) = null,
    @CountryCode varchar(2) = null,
    @PhoneNumber varchar(20) = null,
    @ServiceLevel varchar(35) = null,
    @Page int = 1,
    @ItemsPerPage int = 100
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    BEGIN TRANSACTION;

    SELECT * INTO #temp 
    FROM Locations l
    WHERE (l.LocationName LIKE '%' + @LocationName + '%' OR @LocationName IS NULL)
        AND (l.LocationId LIKE '%' + @LocationId + '%' OR @LocationId IS NULL)
        AND (l.AddressLine1 LIKE '%' + @Address + '%' OR @Address IS NULL)
        AND (l.City LIKE '%' + @City + '%' OR @City IS NULL)
        AND (l.StateProvince LIKE '%' + @StateProvince + '%' OR @StateProvince IS NULL)
        AND (l.PostalCode LIKE '%' + @PostalCode + '%' OR @PostalCode IS NULL)
        AND (l.CountryCode LIKE '%' + @CountryCode + '%' OR @CountryCode IS NULL)
        AND (l.Phone LIKE '%' + @PhoneNumber + '%' OR @PhoneNumber IS NULL)
        AND (l.ServiceLevel LIKE '%' + @ServiceLevel + '%' OR @ServiceLevel IS NULL);

    SELECT COUNT(*) AS TotalCount, 
           CEILING(COUNT(*) / CAST(@ItemsPerPage AS FLOAT)) AS TotalPages 
    FROM #temp;

    SELECT * FROM #temp
        ORDER BY Id
        OFFSET (@Page - 1) * @ItemsPerPage ROWS
        FETCH NEXT @ItemsPerPage ROWS ONLY;

    DROP TABLE #temp;

    COMMIT TRANSACTION;
END
GO

验证步骤 #

1. 执行DBCC USEROPTIONS查看当前会话的隔离级别，确认是否为READ UNCOMMITTED。
2. 在无并发写入的场景下测试存储过程，排查是否是外部事务导致的结果变化。
3. 先尝试添加显式事务和隔离级别配置，观察问题是否缓解。

内容的提问来源于stack exchange，提问作者Dumas.DED