存储过程未按预期执行数据删除操作求助

我编写了一个用于删除数据的存储过程，执行时系统提示“Command completed successfully”，但实际旧数据并未被删除。我添加了判断存储过程是否存在的语句，这部分可以正常生效，但存储过程内的删除逻辑始终未实际执行。以下是我的代码：

USE SkillTracking;
GO

IF object_id('spDataDeletion') IS NULL
    EXEC ('create procedure dbo.spDataDeletion as select 1')
GO

ALTER PROCEDURE spDataDeletion
AS 
BEGIN
    -- 创建临时表存储每个员工的最新记录用于子查询
    CREATE TABLE #LatestEmployeeRecords 
    (
        LogID INT IDENTITY(1,1) PRIMARY KEY,
        HIST_EMP_IFXID varchar(10),
        HIST_AS_CURR_SESS NVARCHAR(100),
        RECORDS_DELETED INT,
        HIST_AS_DATE_UPDATE DATE,
        DELETION_DATE DATE,
        TIMESTAMP DATETIME
    );

    DECLARE @DELETION_DATE DATE = CAST(GETDATE() AS DATE);
    DECLARE @TIMESTAMP DATETIME;
    SELECT @TIMESTAMP = CURRENT_TIMESTAMP

    -- 向临时表插入数据
    INSERT INTO #LatestEmployeeRecords (HIST_EMP_IFXID, HIST_AS_CURR_SESS, RECORDS_DELETED, HIST_AS_DATE_UPDATE, DELETION_DATE, TIMESTAMP)
        SELECT 
            HIST_EMP_IFXID, HIST_AS_CURR_SESS, RECORDS_DELETED, 
            HIST_AS_DATE_UPDATE, @DELETION_DATE, @TIMESTAMP
        FROM  
            (SELECT 
                 HIST_EMP_IFXID, HIST_AS_CURR_SESS, 
                 COUNT(*) AS RECORDS_DELETED, HIST_AS_DATE_UPDATE
             FROM  
                 [dbo].[tblHistoryBackup] 
             WHERE 
                 HIST_AS_DATE_UPDATE < DATEADD(MONTH, -36, @DELETION_DATE)
             GROUP BY 
                 HIST_EMP_IFXID, HIST_AS_CURR_SESS, HIST_AS_DATE_UPDATE) t;

    IF (SELECT COUNT(*) FROM #LatestEmployeeRecords) = 0
    BEGIN
        DECLARE @Message NVARCHAR(4000) = '所有记录均为最新状态。'
            
        -- 插入日志
        INSERT INTO [dbo].[tblLog] (TimeStamp, EventType, Details, IsError)
        VALUES (@TIMESTAMP, '无记录需要删除', @Message, 0);
    END
    ELSE
    BEGIN
        DECLARE @RECORDS_DELETED INT;

        SELECT @RECORDS_DELETED =  SUM(RECORDS_DELETED) 
        FROM #LatestEmployeeRecords

        DECLARE @Message2 NVARCHAR(4000) =  '删除成功。从tblHistoryBackup表共删除 ' + CAST(@RECORDS_DELETED AS NVARCHAR(10)) + ' 条记录';
         
        -- 插入日志
        INSERT INTO [dbo].[tblLog] (TimeStamp, EventType, Details, IsError)
        VALUES (@TIMESTAMP, '删除完成', @Message2, 0);
    END

    DECLARE @DELETION_DATE2 DATE = CAST(GETDATE() AS DATE);
    -- 删除tblHistoryBackup表中的旧数据
    DELETE FROM [dbo].[tblHistoryBackup]
    WHERE HIST_AS_DATE_UPDATE < DATEADD(MONTH, -36, @DELETION_DATE2)

    -- 将删除记录插入日志表
    INSERT INTO [dbo].[tblLog_Data_Deletion](LogID, HIST_EMP_IFXID, HIST_AS_CURR_SESS, RECORDS_DELETED, HIST_AS_DATE_UPDATE, DELETION_DATE, TimeStamp)
        SELECT 
            LogID, HIST_EMP_IFXID, HIST_AS_CURR_SESS, RECORDS_DELETED, 
            HIST_AS_DATE_UPDATE, DELETION_DATE, TIMESTAMP
        FROM 
            #LatestEmployeeRecords;

    -- 删除临时表
    DROP TABLE #LatestEmployeeRecords;
END

• 验证筛选条件是否存在符合的数据
  手动执行以下SQL，确认是否存在36个月前的待删除记录：

  SELECT * FROM [dbo].[tblHistoryBackup] 
  WHERE HIST_AS_DATE_UPDATE < DATEADD(MONTH, -36, CAST(GETDATE() AS DATE))
  

  如果返回空结果，说明没有符合删除条件的数据，这是删除操作无效果的核心原因。

• 检查日志表确认执行分支
  查看tblLog表，如果存在“无记录需要删除”的日志，说明临时表未插入数据，删除逻辑确实没有可操作的对象。

• 添加删除行数验证
  在DELETE语句后添加日志，记录实际删除的行数，方便直接验证操作效果：

  DECLARE @ActualDeletedRows INT = @@ROWCOUNT;
  INSERT INTO [dbo].[tblLog] (TimeStamp, EventType, Details, IsError)
  VALUES (CURRENT_TIMESTAMP, '删除执行详情', '实际删除行数: ' + CAST(@ActualDeletedRows AS NVARCHAR(10)), 0);
  

• 确保存储过程是最新版本
  执行以下语句重新编译存储过程，避免会话缓存导致的旧版本执行：

  EXEC sp_recompile 'spDataDeletion';
  

• 验证权限
  确认执行存储过程的账号对tblHistoryBackup表拥有DELETE权限，权限不足也可能导致删除操作无实际效果。

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