Run stored proc between two dates

Topic

New Reply

HI ALL,

I NEED TO RUN THE SP ON SPECIFIC TIME AND STARTING AND ENDING DATE FOR THIS I FIND A CODE FROM BROWSING IT GIVES ME THE TIME TO RUN BUT HOW SHOULD I DEFINE DATE PARAMETER MEANS SPECIFIC STARTING AND ENDING DATE LIKE 3/1/2010 TO 7/1/2011

CREATE PROCEDURE [dbo].[MyBackgroundTask]
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

— The interval between cleanup attempts
declare @timeToRun nvarchar(50)
set @timeToRun = ’03:33:33′

while 1 = 1
begin
waitfor time @timeToRun
begin
execute [DATABASE].[dbo].[SP];
end

Reply | Quote

Replies

Probably you should use DateTime variables to define start and end times, since they include both date and time, so you can define everything with two simple variables.

To make sure the stored proc runs when you need it, probably the surest way would be to create a job and schedule it with SQL Server Agent.

Reply | Quote

Using SQL Server Agent, as ruirib suggested, to kick off this routine at whatever time of the day you require, you can do this by providing the start and end data as parameters;

Code:

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
CREATE [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]PROCEDURE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [dbo][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][MyBackgroundTask] [/SIZE]
[SIZE=2]
@StartDate [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]DateTime[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]@EndDate [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]DateTime[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
AS

BEGIN
[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]    
[/SIZE][INDENT][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]DECLARE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @DateTimeNow    [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]DateTime[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2]    
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @DateTimeNow [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]= [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]GETDATE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]()[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]IF[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @DateTimeNow [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]BETWEEN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @StartDate [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @EndDate[/SIZE][SIZE=2]

[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]BEGIN[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2] 
[/SIZE][/INDENT]
[INDENT=2][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]EXEC[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [MyDatabaseName][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][dbo][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][MyStoredProcedureName][/SIZE][/INDENT]
[INDENT][SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]END
[/COLOR][/SIZE][/COLOR][/SIZE][/INDENT]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]END

GO
[/COLOR][/SIZE][/COLOR][/SIZE]

If you want to embed the start and end dates in the stored procedure then you can do this;

Code:

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
CREATE [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]PROCEDURE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [dbo][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][MyBackgroundTask] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
AS

BEGIN
[/COLOR][/SIZE][/COLOR][/SIZE][INDENT][SIZE=2][SIZE=2][SIZE=2][COLOR=#0000ff]DECLARE[/COLOR] [/SIZE]@StartDate [COLOR=#0000ff][COLOR=#0000ff]DateTime[/COLOR][/COLOR][COLOR=#808080][COLOR=#808080],[/COLOR][/COLOR][/SIZE][COLOR=#808080][COLOR=#808080]
[/COLOR][/COLOR][SIZE=2][SIZE=2][COLOR=#0000ff]DECLARE[/COLOR] [/SIZE]@EndDate [COLOR=#0000ff][COLOR=#0000ff]DateTime[/COLOR][/COLOR][/SIZE][/SIZE][/INDENT]
[INDENT][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]DECLARE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @DateTimeNow    [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]DateTime[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2]    

[SIZE=2][COLOR=#0000ff]SELECT[/COLOR] @[SIZE=2]StartDate [/SIZE][COLOR=#808080][COLOR=#808080]= ‘4/1/2012’[/COLOR][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]SELECT[/COLOR] @[SIZE=2]EndDate [/SIZE][COLOR=#808080][COLOR=#808080]= ‘4/30/2012’[/COLOR][/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @DateTimeNow [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]= [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]GETDATE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]()[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]IF[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @DateTimeNow [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]BETWEEN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @StartDate [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @EndDate[/SIZE][SIZE=2]

[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]BEGIN[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2] 
[/SIZE][/INDENT]
[INDENT=2][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]EXEC[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [MyDatabaseName][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][dbo][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][MyStoredProcedureName][/SIZE][/INDENT]
[INDENT][SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]END
[/COLOR][/SIZE][/COLOR][/SIZE][/INDENT]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]END

GO
[/COLOR][/SIZE][/COLOR][/SIZE]

Reply | Quote