Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Run stored proc between two dates

    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

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.

  3. #3
    Star Lounger
    Join Date
    Mar 2010
    Location
    Winston-Salem, NC USA
    Posts
    66
    Thanks
    12
    Thanked 4 Times in 4 Posts
    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:
    
    CREATE PROCEDURE [dbo].[MyBackgroundTask] 
    
    @StartDate DateTime,
    @EndDate DateTime
    
    AS
    
    BEGIN
        
    
    DECLARE @DateTimeNow DateTime SELECT @DateTimeNow = GETDATE() IF @DateTimeNow BETWEEN @StartDate AND @EndDate BEGIN
    EXEC [MyDatabaseName].[dbo].[MyStoredProcedureName]
    END
    END GO
    If you want to embed the start and end dates in the stored procedure then you can do this;

    Code:
    
    CREATE PROCEDURE [dbo].[MyBackgroundTask] 
    AS
    
    BEGIN
    
    DECLARE @StartDate DateTime, DECLARE @EndDate DateTime
    DECLARE @DateTimeNow DateTime SELECT @StartDate = '4/1/2012' SELECT @EndDate = '4/30/2012' SELECT @DateTimeNow = GETDATE() IF @DateTimeNow BETWEEN @StartDate AND @EndDate BEGIN
    EXEC [MyDatabaseName].[dbo].[MyStoredProcedureName]
    END
    END GO
    Do your part!!! Report SPAM to http://www.spamcop.net/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •