CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Chickoo
Chickoo

Reputation: 11

Wait for delay within stored procedure

I would like to add wait for delay in my stored procedure after deleting 5 records. @rowstoremove = 5. And I would like to execute this procedure through a job and would like to make sure how many records have been deleted within 1 hour or so.

Any help would be greatly appreciated.

CREATE Procedure [dbo].DeleteRecords
    @rowstoremove int = 0,
    @executeOnce  char(1) = 'Y'
As
Begin
    Declare @err int, 
            @rowCount int,
            @rowsToDelete int

    -- Load records of lowest id for a given duplicate set into a temp table
    select  
        Min(iD) keptID, UserId, AccountId, AddressId 
    into    
        #tmpKept
    from 
        adm.useraccess
    group by 
        UserId, AccountId, AddressId
    order by 1

    select *
    into #tmpRemoved
    from adm.useraccess A
    where not exists (select keptID 
                      from #tmpKept T 
                      where T.keptID = A.ID) 

    select @rowsToDelete = count(*) 
    from #tmpRemoved 

    if @rowstoremove = 0 
       select @rowstoremove = @rowsToDelete

    if @executeOnce = 'Y' 
    begin
        select 
            'Job Starting at ' + convert(varchar(255), getdate()) + '. Deleting a total of ' + convert(varchar(255), @rowstoremove) + ' rows'   

        delete top (@rowstoremove) adm.useraccess   
        from adm.useraccess A, #tmpRemoved R
        where A.ID = R.ID

        set @rowCount  = @@ROWCOUNT

        select 'Duplicate rows removed: ' +  convert(varchar(255), @rowCount) +' on: (' + convert(varchar(255), getdate())  + ')'   

        return
    end

    if @executeOnce !='Y'
    begin
        select 'Job Starting at ' + convert(varchar(255), getdate()) + '. Deleting a total of ' +  convert(varchar(255), @rowsToDelete) +' rows in groups of ' + convert(varchar(255), @rowstoremove)   + ' rows at a time' 

        set @rowCount = 1

        -- Remove duplicate records
        while @rowCount > 0 
        begin
            delete top (@rowstoremove) adm.useraccess   
            from adm.useraccess A, #tmpRemoved R
            where A.ID = R.ID

            set @rowCount = @@ROWCOUNT

            select 'Duplicate rows removed: ' +  convert(varchar(255), @rowCount) +' on: (' + convert(varchar(255), getdate())  + ')'   
        end
    end
end

Upvotes: 1

Views: 2958

Answers (1)

SAS
SAS

Reputation: 4045

Use waitfor (https://msdn.microsoft.com/en-us/library/ms187331.aspx):

WAITFOR 
{
    DELAY 'time_to_pass' 
  | TIME 'time_to_execute' 
  | [ ( receive_statement ) | ( get_conversation_group_statement ) ] 
    [ , TIMEOUT timeout ]
}

Upvotes: 1

Related Questions