时间戳

首页 > 解决方案 > 数据归档不工作时的动态 SQL 日期时间比较

sql - 数据归档不工作时的动态 SQL 日期时间比较

问题描述

我已经为我的一张表中的数据清除编写了这个存储过程。我已将其配置为存档数据或进行硬删除。由于数据量很大，我正在使用循环来做同样的事情。它的删除部分工作正常但是它的存档部分给我带来了困难，我多次尝试被困在那里。这是我的SP。

ALTER PROCEDURE [dbo].[spPurgeRecords_new] (
 @Age AS INT,
 @NumberOfLoops AS BIGINT,
 @DeleteSize BIGINT,
 @IsArchive BIT
)
AS

SET NOCOUNT ON
SET XACT_ABORT ON

BEGIN

DECLARE @CurrentLoop AS BIGINT;
SET @CurrentLoop = 0

declare @cutoffDate datetime;
declare @maxXDate datetime;
declare @loop varchar(50);

select @cutoffDate = dateadd(year,-@Age,getdate())
select @maxXDate = max(dateCreated)  from cbr_audit where dateCreated < @cutoffDate

declare @date varchar(100), @cmd varchar(1000),@archivedate varchar(100)

set @date = (select FORMAT(getdate(), 'yyyyMMdd'));
set @archivedate = (select FORMAT(@maxXDate, 'yyyyMMdd'));

declare @backupTable varchar(100)
set @backupTable =  'cbr_audit_Backup_' + @date;

BEGIN TRY
    BEGIN TRANSACTION   
    WHILE @CurrentLoop < @NumberOfLoops
    BEGIN       
             IF @IsArchive = 1
                          BEGIN
                         --Archive the records into a backup table
                          IF OBJECT_ID (@backupTable, N'U') IS NULL 
                              begin
                                 set @cmd = 'SELECT * INTO [cbr_audit_Backup_'+ @date +'] FROM [cbr_audit] WITH (NOLOCK) where convert(datetime,dateCreated,101) <=  CONVERT(DATETIME, ''' + @archivedate + ''', 101)'
                                 exec(@cmd)
                              end

                             --Delete the rows from cbr_audit table
                             DELETE 
                                FROM dbo.cbr_audit
                                WHERE id IN 
                                    (SELECT TOP(@DeleteSize) id
                                     FROM dbo.cbr_audit WITH (NOLOCK)
                                     WHERE dateCreated  <= @maxXDate);                      

                         END

             ELSE
                         BEGIN
                        -- Delete the records
                            DELETE 
                                FROM dbo.cbr_audit
                                WHERE id IN 
                                    (SELECT TOP(@DeleteSize) id
                                     FROM dbo.cbr_audit WITH (NOLOCK)
                                     WHERE dateCreated  <= @maxXDate);

                            END
                                  -- WAITFOR DELAY '00:00:00:500';        
                SET @CurrentLoop = @CurrentLoop + 1;            
                set @loop = cast(@currentloop as varchar(50))       
                RAISERROR (@loop, 0, 1) WITH NOWAIT 
    END
COMMIT TRANSACTION  
END TRY
BEGIN CATCH     
--Rollback     
                RETURN
 END CATCH
END

在此 SP 中，在代码的存档部分中，动态 sql 没有给出任何结果。DateCreated 是 Datetime 类型。有人可以帮我解决这个问题。提前致谢。

标签： sqlsql-serverdynamic-sqldatabase-administration

解决方案

似乎您使用的是 SQL 2016 或更高版本，请尝试 CONCAT，例如： 

select concat('SELECT * INTO [cbr_audit_Backup_', @date,'] FROM [cbr_audit] WITH (NOLOCK) where convert(datetime,dateCreated,121) <=  ''', CONVERT(varchar(30), @archivedate , 121), '''')

还有几件事：我强烈建议您始终使用样式 121（规范）

  • 或 126 (ISO8601)，这样您的 SQL 就不会被 mm/dd/yyyy (101) 或 dd/mm/yyyy (103) 混淆。
  • 您正在按块删除，但事务是针对所有块的。考虑为每个删除只做事务（隐式）
  • 而是插入然后删除，在这里查看 OUTPUT 子句以删除

推荐阅读