SQL Server存储过程临时表残留及日期查询异常求助

环境与原始代码 #

数据表结构 #

CREATE TABLE [dbo].[AttendanceRecords]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SampleDate] [date] NULL,
    [Weekday] [nvarchar](20) NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [Department] [nvarchar](50) NULL,
    [Area] [nvarchar](20) NULL,
    [TotalTime] [int] NULL,
    [FirstInTime] [datetime] NULL,
    [LastOutTime] [datetime] NULL,
    [GrossTotalTime] [int] NULL
) ON [PRIMARY]

原始存储过程 #

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetGeneralAttendanceData]
(
    @FirstName nvarchar(50) = null,
    @LastName nvarchar(50) = null,
    @StartDate datetime = null,
    @EndDate datetime = null,
    @Department nvarchar(50) = null,
    @PartialName nvarchar(50) = null
)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #finalTable (FirstName NVARCHAR(50), LastName NVARCHAR(50));
    CREATE TABLE #secondaryTable (FirstName NVARCHAR(50), LastName NVARCHAR(50));
    
    SELECT *
    INTO #TimeCards
    FROM AttendanceRecords
    WHERE 1=1 
        AND (@FirstName is NULL or FirstName = @FirstName)
        AND (@LastName is NULL or LastName = @LastName)
        AND (@StartDate is NULL or SampleDate >= @StartDate)
        AND (@EndDate is NULL or SampleDate <= @EndDate)
        AND (@Department is NULL or Department = @Department)
        AND (@PartialName IS NULL OR FirstName LIKE '%' + @PartialName + '%' OR LastName LIKE '%' + @PartialName + '%')
    ORDER BY SampleDate;

    DECLARE @cols NVARCHAR(MAX) = null;
    DECLARE @colsWithoutWeekdays NVARCHAR(MAX) = null;
    DECLARE @colsAndTypes NVARCHAR(MAX) = null;
    DECLARE @query NVARCHAR(MAX) = null;
    DECLARE @SQL NVARCHAR(MAX) = null;
    
    SELECT @cols = STUFF((SELECT ',' + QUOTENAME(d.SampleDate)
                        FROM
                        (
                            SELECT convert(nvarchar(25), SampleDate,25) as SampleDate
                            FROM #TimeCards
                        ) d
                        GROUP BY SampleDate
                        ORDER BY SampleDate
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)')
                    ,1,1,'');

    SELECT @colsAndTypes = STUFF((SELECT ',' + QUOTENAME(d.SampleDate) + ' NVARCHAR(50)'
                        FROM
                        (
                            SELECT convert(nvarchar(25), SampleDate,25)+' - '+ [WeekDay] as SampleDate
                            FROM #TimeCards
                        ) d
                        GROUP BY SampleDate
                        ORDER BY SampleDate
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)')
                    ,1,1,'');

    SET @SQL = 'ALTER TABLE #finalTable ADD ' + @colsAndTypes;
    EXECUTE(@SQL);

    SET @SQL = 'ALTER TABLE #secondaryTable ADD ' + @colsWithoutWeekdays;
    EXECUTE(@SQL);

    SET @query = 'SELECT FirstName, LastName, ' + @cols + '
                  FROM
                  (SELECT FirstName, LastName, SampleDate, TotalTime FROM #TimeCards) x
                  pivot
                  (sum(TotalTime) for SampleDate in (' + @cols + ')) p
                  order by LastName, FirstName';
    
    SET @SQL = 'INSERT INTO #finalTable ' + @query;
    EXECUTE(@SQL);

    SET @SQL = 'SELECT * FROM #finalTable';
    EXECUTE(@SQL);

    SET @SQL = 'DROP TABLE #finalTable';
    EXECUTE(@SQL);
    SET @SQL = 'DROP TABLE #secondaryTable';
    EXECUTE(@SQL);
    SET @SQL = 'DROP TABLE #TimeCards';
    EXECUTE(@SQL);

    IF OBJECT_ID('tempdb..#finalTable') IS NOT NULL DROP TABLE #finalTable;
    IF OBJECT_ID('tempdb..#secondaryTable') IS NOT NULL DROP TABLE #secondaryTable;
    IF OBJECT_ID('tempdb..#TimeCards') IS NOT NULL DROP TABLE #TimeCards;
END
GO

问题现象 #

• 存储过程多次执行后数据残留：扩大日期范围时，仍返回之前小范围的查询结果
• 缩小日期范围首次执行报错：

Msg 0, Level 11, State 0, Line 2
A severe error occurred on the current command. The results, if any, should be discarded.
再次执行则恢复正常

问题根源分析 #

1. 未初始化变量引发语法错误：@colsWithoutWeekdays始终为NULL，执行ALTER TABLE #secondaryTable ADD + NULL会触发语法错误，中断执行流程，导致临时表未被正确删除，残留到下一次执行
2. 临时表元数据缓存冲突：SQL Server会缓存临时表的元数据，动态修改临时表结构后，后续执行可能复用旧元数据，导致结构不匹配，引发报错或数据残留
3. 动态SQL作用域问题：动态SQL的子作用域与主作用域共享临时表，但元数据缓存会导致结构不一致，引发逻辑冲突
4. 冗余删除操作无效：执行中断时部分删除逻辑无法触发，导致临时表残留

修复方案 #

修改后的存储过程代码 #

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetGeneralAttendanceData]
(
    @FirstName nvarchar(50) = null,
    @LastName nvarchar(50) = null,
    @StartDate datetime = null,
    @EndDate datetime = null,
    @Department nvarchar(50) = null,
    @PartialName nvarchar(50) = null
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @cols NVARCHAR(MAX);
    DECLARE @pivotCols NVARCHAR(MAX);
    DECLARE @query NVARCHAR(MAX);

    -- 获取带星期的显示列名
    SELECT @cols = STUFF((
        SELECT ',' + QUOTENAME(CONVERT(nvarchar(25), SampleDate, 25) + ' - ' + [WeekDay])
        FROM (
            SELECT DISTINCT SampleDate, [WeekDay]
            FROM AttendanceRecords
            WHERE 1=1 
                AND (@FirstName IS NULL OR FirstName = @FirstName)
                AND (@LastName IS NULL OR LastName = @LastName)
                AND (@StartDate IS NULL OR SampleDate >= @StartDate)
                AND (@EndDate IS NULL OR SampleDate <= @EndDate)
                AND (@Department IS NULL OR Department = @Department)
                AND (@PartialName IS NULL OR FirstName LIKE '%' + @PartialName + '%' OR LastName LIKE '%' + @PartialName + '%')
        ) d
        ORDER BY SampleDate
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

    -- 获取透视用的原始日期列名
    SELECT @pivotCols = STUFF((
        SELECT ',' + QUOTENAME(CONVERT(nvarchar(25), SampleDate, 25))
        FROM (
            SELECT DISTINCT SampleDate
            FROM AttendanceRecords
            WHERE 1=1 
                AND (@FirstName IS NULL OR FirstName = @FirstName)
                AND (@LastName IS NULL OR LastName = @LastName)
                AND (@StartDate IS NULL OR SampleDate >= @StartDate)
                AND (@EndDate IS NULL OR SampleDate <= @EndDate)
                AND (@Department IS NULL OR Department = @Department)
                AND (@PartialName IS NULL OR FirstName LIKE '%' + @PartialName + '%' OR LastName LIKE '%' + @PartialName + '%')
        ) d
        ORDER BY SampleDate
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

    -- 构建动态透视查询，直接返回结果
    SET @query = N'
        SELECT FirstName, LastName, ' + @cols + N'
        FROM (
            SELECT 
                FirstName, 
                LastName,
                CONVERT(nvarchar(25), SampleDate, 25) + '' - '' + [WeekDay] AS DisplayDate,
                CONVERT(nvarchar(25), SampleDate, 25) AS PivotDate,
                TotalTime
            FROM AttendanceRecords
            WHERE 1=1 
                AND (@FirstName IS NULL OR FirstName = @FirstName)
                AND (@LastName IS NULL OR LastName = @LastName)
                AND (@StartDate IS NULL OR SampleDate >= @StartDate)
                AND (@EndDate IS NULL OR SampleDate <= @EndDate)
                AND (@Department IS NULL OR Department = @Department)
                AND (@PartialName IS NULL OR FirstName LIKE ''%'' + @PartialName + ''%'' OR LastName LIKE ''%'' + @PartialName + ''%'')
        ) x
        PIVOT (
            SUM(TotalTime) 
            FOR PivotDate IN (' + @pivotCols + N')
        ) p
        ORDER BY LastName, FirstName;
    ';

    -- 执行动态查询并传递参数
    EXEC sp_executesql @query, 
        N'@FirstName nvarchar(50), @LastName nvarchar(50), @StartDate datetime, @EndDate datetime, @Department nvarchar(50), @PartialName nvarchar(50)',
        @FirstName = @FirstName,
        @LastName = @LastName,
        @StartDate = @StartDate,
        @EndDate = @EndDate,
        @Department = @Department,
        @PartialName = @PartialName;
END
GO

核心修改说明 #

• 移除无用的#secondaryTable相关逻辑，避免未初始化变量引发的语法错误
• 直接基于源表生成透视列，删除中间临时表，彻底解决元数据缓存冲突问题
• 使用sp_executesql传递参数，避免SQL注入风险，同时保证参数传递的正确性
• 利用存储过程结束时自动清理临时对象的特性，移除冗余的临时表删除操作

额外说明 #

• 临时表存储位置：本地临时表（#开头）存储在tempdb中，每个会话的临时表会被添加唯一后缀，会话结束后自动删除
• 关于"残留"的误解：你提到的执行后需手动删除不是事务问题，是临时表的元数据缓存导致的结构不一致，改用直接透视的方式可完全避免该问题

内容的提问来源于stack exchange，提问作者Mr. Bill