Styjun

What is the name for the average of the largest and the smallest values in a given data set?

Do pedestrians imitate auto traffic?

Do Australia and New Zealand have a travel ban on Somalis (like Wikipedia says)?

Is it legal for a supermarket to refuse to sell an adult beer if an adult with them doesn’t have their ID?

Should I have shared a document with a former employee?

"Je suis petite, moi?", purpose of the "moi"?

Improving an O(N^2) function (all entities iterating over all other entities)

When designing an adventure, how can I ensure a continuous player experience in a setting that's likely to favor TPKs?

What is a Romeo Word™?

Ethernet to USB adapter on Linux

What is this green alien supposed to be on the American covers of the "Hitchhiker's Guide to the Galaxy"?

Why do jet engines sound louder on the ground than inside the aircraft?

How much solution to fill Paterson Universal Tank when developing film?

Who determines when road center lines are solid or dashed?

Why is an object not defined as identity morphism?

Will the internet speed decrease on second router if there are multiple devices connected to primary router?

Why teach C using scanf without talking about command line arguments?

Inscriptio Labyrinthica

Which failed attempts have there been to find a contradiction in ZFC or ZF?

Are more expensive chains/casettes more quiet?

Three Subway Escalators

Is it possible to have a career in SciComp without contributing to arms research?

Why do space operations use "nominal" to mean "working correctly"?

I have found a mistake on someone's code published online: what is the protocol?

Can I use “WAITFOR DELAY” to execute a procedure

How can I remove duplicate rows?How can foreign key constraints be temporarily disabled using T-SQL?Insert results of a stored procedure into a temporary tableFunction vs. Stored Procedure in SQL ServerHow can I do an UPDATE statement with JOIN in SQL?Getting value from stored procedure in another stored procedureAccess Stored Procedure Parameters with Dynamic SQLTransact-SQL stored procedure with conditional “AND” logicMerge stored procedure with datatype conversionsSQL Stored Procedure if statement with bit

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

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

tsql

share|improve this question

asked Mar 26 at 10:47

ArkadiuszArkadiusz

8088 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  why not a job scheduled every 10 minutes?
  
  – PSK
  Mar 26 at 10:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  But I don't want a job to start every 10 min all the time, I'd like to stop it if condition is true. CanI do it using a job?
  
  – Arkadiusz
  Mar 26 at 10:57
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  yes, if you don't want the job to run again you can use sp_stop_job
  
  – PSK
  Mar 26 at 10:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That's what I want.
  
  – Arkadiusz
  Mar 26 at 11:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  sp_stop_job procedure stops executing running job but it doesn't alter the fact that the job will fire according to the schedule.
  
  – Arkadiusz
  Mar 26 at 11:46
  

  
  
  
  

 | 
show 3 more comments

0

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

tsql

share|improve this question

asked Mar 26 at 10:47

ArkadiuszArkadiusz

8088 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  why not a job scheduled every 10 minutes?
  
  – PSK
  Mar 26 at 10:52
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  But I don't want a job to start every 10 min all the time, I'd like to stop it if condition is true. CanI do it using a job?
  
  – Arkadiusz
  Mar 26 at 10:57
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  yes, if you don't want the job to run again you can use sp_stop_job
  
  – PSK
  Mar 26 at 10:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That's what I want.
  
  – Arkadiusz
  Mar 26 at 11:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  sp_stop_job procedure stops executing running job but it doesn't alter the fact that the job will fire according to the schedule.
  
  – Arkadiusz
  Mar 26 at 11:46
  

  
  
  
  

 | 
show 3 more comments

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

tsql

share|improve this question

asked Mar 26 at 10:47

ArkadiuszArkadiusz

8088 bronze badges

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

share|improve this question

asked Mar 26 at 10:47

ArkadiuszArkadiusz

8088 bronze badges

share|improve this question

asked Mar 26 at 10:47

ArkadiuszArkadiusz

8088 bronze badges

asked Mar 26 at 10:47

ArkadiuszArkadiusz

8088 bronze badges

asked Mar 26 at 10:47

ArkadiuszArkadiusz

8088 bronze badges

1 Answer
1

active

oldest

votes

1

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

share|improve this answer

answered Mar 26 at 15:23

Eric BrandtEric Brandt

3,96311 gold badge1212 silver badges2828 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm of opinion that it's a little bit confusing that "Retry Attempts" feature is below "On success action" section. How is this feature supposed to work?
  
  – Arkadiusz
  Mar 27 at 8:34
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Yes, that tab isn't very intuitive. Once you've set it up a couple of times, though, it's easy to remember. Just select the number of minutes to wait between retries (in your case, you want a ten minute wait), and then select the number of times you want it to retry. It sounds like you want that to be a pretty large number. Here's a short blog post with a corresponding screen shot: sqldbpool.com/2014/06/01/…
  
  – Eric Brandt
  Mar 27 at 13:15
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55355265%2fcan-i-use-waitfor-delay-to-execute-a-procedure%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

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

share|improve this answer

answered Mar 26 at 15:23

Eric BrandtEric Brandt

3,96311 gold badge1212 silver badges2828 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm of opinion that it's a little bit confusing that "Retry Attempts" feature is below "On success action" section. How is this feature supposed to work?
  
  – Arkadiusz
  Mar 27 at 8:34
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Yes, that tab isn't very intuitive. Once you've set it up a couple of times, though, it's easy to remember. Just select the number of minutes to wait between retries (in your case, you want a ten minute wait), and then select the number of times you want it to retry. It sounds like you want that to be a pretty large number. Here's a short blog post with a corresponding screen shot: sqldbpool.com/2014/06/01/…
  
  – Eric Brandt
  Mar 27 at 13:15
  

  
  
  
  

add a comment | 

1

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

share|improve this answer

answered Mar 26 at 15:23

Eric BrandtEric Brandt

3,96311 gold badge1212 silver badges2828 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm of opinion that it's a little bit confusing that "Retry Attempts" feature is below "On success action" section. How is this feature supposed to work?
  
  – Arkadiusz
  Mar 27 at 8:34
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Yes, that tab isn't very intuitive. Once you've set it up a couple of times, though, it's easy to remember. Just select the number of minutes to wait between retries (in your case, you want a ten minute wait), and then select the number of times you want it to retry. It sounds like you want that to be a pretty large number. Here's a short blog post with a corresponding screen shot: sqldbpool.com/2014/06/01/…
  
  – Eric Brandt
  Mar 27 at 13:15
  

  
  
  
  

add a comment | 

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

share|improve this answer

answered Mar 26 at 15:23

Eric BrandtEric Brandt

3,96311 gold badge1212 silver badges2828 bronze badges

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

share|improve this answer

answered Mar 26 at 15:23

Eric BrandtEric Brandt

3,96311 gold badge1212 silver badges2828 bronze badges

answered Mar 26 at 15:23

Eric BrandtEric Brandt

3,96311 gold badge1212 silver badges2828 bronze badges

answered Mar 26 at 15:23

Eric BrandtEric Brandt

3,96311 gold badge1212 silver badges2828 bronze badges