The End of the Matter:

ALTER PROCEDURE [dbo].[StartBookingReminders]

-- Add the parameters for the stored procedure here

@RunsEvery Int,

@ServerName VARCHAR(30),

@DatabaseName VARCHAR(128)

AS

BEGIN

Exec dbo.StopBookingReminders

exec msdb.dbo.sp_add_job @job_name = 'CS-Booking_Reminders',

@enabled=1

exec msdb.dbo.sp_add_jobstep

@job_name = 'CS-Booking_Reminders',

@step_name = 'Check for reminders to send',

@subsystem = 'TSQL',

@command = 'exec dbo.SendBookingReminders',

@database_name = @databaseName

exec msdb.dbo.sp_add_schedule

@schedule_name = 'CS-Booking_Reminders_Schedule',

@enabled = 1,

@freq_interval = 1,

@freq_type = 4,

@freq_subday_type = 8,

@freq_recurrence_factor = 1,

@freq_subday_interval = @RunsEvery

exec msdb.dbo.sp_attach_schedule

@job_name = 'CS-Booking_Reminders',

@schedule_name = 'CS-Booking_Reminders_Schedule'

exec msdb.dbo.sp_add_jobserver

@job_name = 'CS-Booking_Reminders',

@server_name = @ServerName

END

The trick is in getting the Add Schedule call correct. To get something to happen each hour every day, you need to set:

@freq_interval = 1,

@freq_type = 4,

@freq_subday_type = 8,

@freq_recurrence_factor = 1,

@freq_subday_interval = @RunsEvery

The scheduler looks at the @freq_interval and @freq_type to decide which days to run it on, then, on those days, it looks at @freq_subday_type and @freq_subday_interval to decide when to run it on those days.

If your recurrence_faction is 0, the scheduler will run it the first time on the first day, and not again.