06/01: Bunch Delete In SQL Server

 

BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        DECLARE @intErrorCode INT

        begin transaction
            --SAP_FDX_Detail
            WHILE(1=1)
                Begin
                delete
                top(10000) from [SAP_FDX_Detail] where date_added<DateAdd(day,-180, GetDate())
                IF @@ROWCOUNT=0
                Begin
                    print 'SAP_FDX_Detail Records Has Been Purged' 
                    Break
                end
                end
                SELECT @intErrorCode = @@ERROR
                IF (@intErrorCode <> 0) GOTO PROBLEM

             -- SAP_FDX_Header
            WHILE(1=1)
                Begin
                delete
                top(10000) from [SAP_FDX_Header] where date_added<DateAdd(day,-180, GetDate())
                IF @@ROWCOUNT=0
                Begin
                    print 'SAP_FDX_Header Records Has Been Purged' 
                    Break
                end
                end
                SELECT @intErrorCode = @@ERROR
                IF (@intErrorCode <> 0) GOTO PROBLEM
    --SAP_FDX_Trailer
            WHILE(1=1)
                Begin
                delete
                top(10000) from [SAP_FDX_Trailer] where date_added<DateAdd(day,-180, GetDate())
                IF @@ROWCOUNT=0
                Begin
                    print 'SAP_FDX_Trailer Records Has Been Purged' 
                    Break
                end
                end
                SELECT @intErrorCode = @@ERROR
                IF (@intErrorCode <> 0) GOTO PROBLEM

      commit transaction

      PROBLEM:
        IF (@intErrorCode <> 0) BEGIN
        PRINT 'Unexpected error occurred!'
            ROLLBACK TRAN
        END

END