Use tsql to create a job

The original post source csdn, the specific post cannot be remembered, And I modified it a bit based on the original one:

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_createjob] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_createjob]
Go

Create proc p_createjob
@ Jobname varchar (100), -- job name
@ SQL varchar (8000), -- command to be executed
@ Servername sysname = '', -- job server name
@ Dbname sysname = '', -- the default value is the current database name.
@ Freqtype varchar (6) = 'day', -- time period, month, week, day
@ Fsinterval Int = 1, -- the number of repetitions relative to the day
@ Time Int = 170000 -- execution start time. For repeated jobs, the time ranges from 0.
As
If isnull (@ dbname, '') ='' set @ dbname = db_name ()

-- Create a job
Exec MSDB .. sp_add_job @ job_name = @ jobname

-- Create a job
Exec MSDB .. sp_add_jobstep @ job_name = @ jobname,
@ Step_name = 'data ',
@ Subsystem = 'tsql ',
@ Database_name = @ dbname,
@ Command = @ SQL,
@ Retry_attempts = 5, -- number of retries
@ Retry_interval = 5 -- Retry Interval

-- Create Scheduling
Declare @ FTYPE int, @ fstype int, @ ffactor int
Select @ FTYPE = case @ freqtype when 'day' then 4
When 'Week' then 8
When 'month' then 16 end
, @ Fstype = case @ fsinterval when 1 then 0 else 8 end
If @ fsinterval <> 1 Set @ time = 0
Set @ ffactor = case @ freqtype when 'day' then 0 else 1 end

Exec MSDB .. sp_add_jobschedule @ job_name = @ jobname,
@ Name = 'schedule ',
@ Freq_type = @ FTYPE, -- every day, 8 weeks, 16 months
@ Freq_interval = 1, -- number of repeated executions
@ Freq_subday_type = @ fstype, -- whether to execute repeatedly
@ Freq_subday_interval = @ fsinterval, -- recurrence
@ Freq_recurrence_factor = @ ffactor,
@ Active_start_time = @ time -- execute at 17:00:00 pm

If @ servername =''
Set @ servername = @ servername
Exec MSDB .. sp_add_jobserver @ job_name = @ jobname,
@ SERVER_NAME = @ servername

Go

-- Call
-- Jobs executed every month
Exec p_createjob @ jobname = 'mm'
, @ SQL = 'insert a select ''' AAA '''
, @ Servername = 'fudan-oxi9y1pyt'
, @ Dbname = 'test'
, @ Freqtype = 'month'
, @ Time = '000000'