CODEWITHSUNDEEP
t-sql
Arkadiusz
Arkadiusz

Reputation: 427

Can I use "WAITFOR DELAY" to execute a procedure

I'm wondering if it's possible to execute a procedure inside the same procedure. I wouldn't like to use a trigger so what I'm trying to do is to execute the same procedure in a loop if condition is false after 10 minutes.

use CF_BPS
go

create procedure p_Monitoring_HLR_statusy
as

if (
(select sum(uruchom_api) as sprawdzenie from ANALIZY..CC_LISTA_SMS_API
where priorytet=0 and cast(aud_data as date)=cast(getdate() as   
date))=0)    

begin

        insert into [CF_BPS].[dbo].[Monitoring_HLR_statusy]
        ([sp_id], [sp_numer], [tn_numer], [sms_api_check_date], [sms_api_phone], [sms_api_status])

        select [sp_id], [sp_numer], [telefon], [sms_api_check_date], [sms_api_phone], [sms_api_status] from ANALIZY..CC_LISTA_SMS_API
        where priorytet=0 and cast(aud_data as date)=cast(getdate() as date)

end

    else

begin

        waitfor delay '00:10:00.000'
        execute p_Monitoring_HLR_statusy

end

go

Upvotes: 0

Views: 445

Answers (1)

Eric Brandt
Eric Brandt

Reputation: 8111

In order to avoid the recursion issue, and to just make the logic more transparent, I'd break this into two pieces. In SQL Agent, this is dead simple. Probably not much harder in any other scheduling tool.

First, have the job execute your procedure. Instead of the WAITFOR, though, have the ELSE clause THROW an error. (Note the semi-colon after BEGIN; it's a requirement for THROW.)

Then set up your job scheduler to retry the failed job after a 10 minute delay, and set a limit for the number of retries so you don't accidentally put yourself in a situation where the job just keeps trying even if your data never shows up.

The revised proc looks like this:

use CF_BPS
go

create procedure p_Monitoring_HLR_statusy
as

if (
(select sum(uruchom_api) as sprawdzenie from ANALIZY..CC_LISTA_SMS_API
where priorytet=0 and cast(aud_data as date)=cast(getdate() as   
date))=0)  

    begin;

      insert into [CF_BPS].[dbo].[Monitoring_HLR_statusy]
      ([sp_id], [sp_numer], [tn_numer], [sms_api_check_date], [sms_api_phone], [sms_api_status])

      select [sp_id], [sp_numer], [telefon], [sms_api_check_date], [sms_api_phone], [sms_api_status] from ANALIZY..CC_LISTA_SMS_API
      where priorytet=0 and cast(aud_data as date)=cast(getdate() as date);

    end

  else

    begin;

      THROW 51000, 'The data does not exist yet.', 1;

    end

Upvotes: 1

Related Questions