Scheduling a SQL Job using sp_add_jobschedule-CodePudding

I was trying to add a job using sp_add_jobschedule. I was also given a very good link yesterday where I got some clues on how to create a job. While creating the stored procedure I am getting an error:

Msg 102, Level 15, State 1, Procedure Usp_Create_job, Line 19 [Batch Start Line 81]
Incorrect syntax near ';'.
Msg 2812, Level 16, State 62, Line 103
Could not find stored procedure 'sp_add_jobstep'.
Msg 2812, Level 16, State 62, Line 118
Could not find stored procedure 'dbo.sp_add_jobserver'.
Msg 137, Level 15, State 2, Line 127
Must declare the scalar variable "@Frequency".

I have used the following code to create the Stored Procedure.

create procedure Usp_Create_job
as
begin

declare @SP_Name varchar(max) 
declare @JobName varchar(max) 
declare @Step varchar(max)
declare @ScheduleName varchar(max)
declare @Frequency int
declare @Interval int
declare @Time int
declare @StartDate int

EXEC dbo.sp_add_job  
    @job_name = N'@JobName', -- passing job name through input parameter while executing SP Usp_Create_job
    @category_name=N'[Uncategorized (Local)]',
    @owner_login_name=N'sa',
    @notify_level_eventlog=0 ;
GO 

EXEC sp_add_jobstep  
    @job_name = N'@JobName',   --passing job name through input parameter while executing SP Usp_Create_job
    @step_id = 1, 
    @step_name = N'@Step',     --passing step name through input parameter while executing SP Usp_Create_job
    @subsystem = N'TSQL',  
    @command = N'

-- actual query
exec @SP_Name',   
    @retry_attempts = 0,  
    @retry_interval = 0,
    @on_success_action = 1,
    @database_name=N'Test' ;
GO  

EXEC dbo.sp_add_jobserver  
    @job_name = N'@JobName',    --passing job name through input parameter while executing SP Usp_Create_job
    @server_name = N'(local)' ;
GO 

EXEC sp_add_jobschedule 
    @job_name = N'@JobName',
    @name = N'test job schedule', 
    @enabled = 1,   --enabled
    @freq_type = @Frequency, -- on daily basis input value 4
    @freq_interval = @Interval, 
    @freq_subday_type = 1,  -- units between each exec: seconds
    @freq_subday_interval = 0,  -- number of units between each exec
    @active_start_date= @StartDate, 
    @active_end_date=99991231, 
    @schedule_uid=N'8912aa53-ffe9-4f31-b6cb-9a8e2f1ee6e3'

end

I have reviewed the code couple of times but was unable to find the exact issue. Any improvements will also be appreciated.

CodePudding user response：

There are a number of issues with your code.

You can't have GO in the middle of a procedure, it signifies the end of a batch and a procedure must be a single batch.
The procedures are located in msdb, and it seems you want to create the procedure in that database. You need to switch to that database first.
You haven't defined parameters properly, you have just made local variables.
You cannot pass parameters contained in quotes, as that just means it will be looked at as a bare string such as '@SP_Name'
Some data types are wrong.
@schedule_uid is an output parameter, you do not need to pass it.
You are not passing in @ScheduleName.
The value for @active_end_date should be in quotes.

USE msdb;

GO

CREATE OR ALTER PROCEDURE Usp_Create_job
  @SP_Name sysname
  @JobName sysname,
  @Step sysname,
  @ScheduleName sysname,
  @Frequency int,
  @Interval int,
  @Time int,
  @StartDate int
AS

DECLARE @command nvarchar(max) = @command = N'
-- actual query
exec '   QUOTENAME(@SP_Name)   '
-- more commands here
'

EXEC sp_add_job  
    @job_name = @JobName, -- passing job name through input parameter while executing SP Usp_Create_job
    @category_name = N'[Uncategorized (Local)]',
    @owner_login_name = N'sa',
    @notify_level_eventlog = 0;

EXEC sp_add_jobstep
    @job_name = @JobName,   --passing job name through input parameter while executing SP Usp_Create_job
    @step_id = 1, 
    @step_name = @Step,     --passing step name through input parameter while executing SP Usp_Create_job
    @subsystem = N'TSQL',  
    @command = @command,   
    @retry_attempts = 0,  
    @retry_interval = 0,
    @on_success_action = 1,
    @database_name = N'Test';

EXEC dbo.sp_add_jobserver
    @job_name = @JobName,    --passing job name through input parameter while executing SP Usp_Create_job
    @server_name = N'(local)' ;

EXEC sp_add_jobschedule 
    @job_name = @JobName,
    @name = @ScheduleName,
    @enabled = 1,   --enabled
    @freq_type = @Frequency, -- on daily basis input value 4
    @freq_interval = @Interval, 
    @freq_subday_type = 1,  -- units between each exec: seconds
    @freq_subday_interval = 0,  -- number of units between each exec
    @active_start_date = @StartDate, 
    @active_end_date = '99991231';

GO

Exactly why you would want this rather than just using SSMS's nice graphical interface is a different question...