We help IT Professionals succeed at work.

Execute stored procedure during time period

Medium Priority
236 Views
Last Modified: 2012-05-12
I have a job that runs every 10 minutes on our system and executes a series of stored procedures

I have a couple (out of the 20 or so) that I need to execute ONLY between 9am and 6pm daily.

How do I do this...

I don;t want to create anothe job
Comment
Watch Question

Senior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016
Commented:
you can check at the beginning of your stored procedure:

DECLARE @hour INT
SET @hour = DATEPART(HOUR, GETDATE())

IF @hour >= 9 AND @hour < 18
BEGIN
      SELECT 'in the range...'
END
ELSE
BEGIN
      SELECT 'get out of here!'
END
CERTIFIED EXPERT
Top Expert 2011

Commented:
probably the easiest option would be to create a new stored procedure which will invoke the existing one(s)

so it needs all the same parameters as the existing procedure(s) and maybe a couple of extra optional ones

put the time test into the new procedure

ie

if hour(getdate()) between 9 and 17
   or  @runflag = 'Y'      -- a new parameter to allow for testing basically or special runs...
begin
     exec @rc = yourexistingstoredprocedure @parm1,@parm2
     ...
end
else
begin
     print 'procedure xxxxx not called out of run hours 9 - 18'
end

return

you could set up a table to control the run times which may allow for easier job control/maintenance

but you are essentially duplicating what is already available via the job scheduling system...
Larry Bristersr. Developer

Author

Commented:
Thanks