When you create a SQL Server Agent job with T-SQL, you use the sp_add_jobstep stored procedure to add each job step.

If you ever need to add a new step to that job, you can use sp_add_jobstep once again to add the new job step.

But you may also need to modify the existing step, depending on how you’d like the job to progress through the steps.

Example

In this example, we create a job with one job step, then we use sp_add_jobstep to add a second step to that job. We then use sp_update_jobstep to update the first job step so that it progresses to the second job step as soon as it completes.

Create the Job

First, create the job with one step:

USE msdb;  
GO  
EXEC sp_add_job  
    @job_name = N'SqlAgentTest',
    @description = N'Insert data.',
    @category_name = 'Database Maintenance';
GO  
EXEC sp_add_jobstep  
    @job_name = N'SqlAgentTest',  
    @step_name = N'Insert data for step 1',  
    @subsystem = N'TSQL',  
    @command = N'USE TestDB; INSERT INTO SqlAgentJobs VALUES (''SqlAgentTest'', 1, SYSDATETIME())';
GO
EXEC sp_add_schedule 
    @schedule_name = N'Run_Sat_6AM',
    @freq_type = 8,
    @freq_interval = 64,
    @freq_recurrence_factor = 1,
    @active_start_time = 060000;
GO  
EXEC sp_attach_schedule  
   @job_name = N'SqlAgentTest',  
   @schedule_name = N'Run_Sat_6AM';
GO  
EXEC sp_add_jobserver  
    @job_name = N'SqlAgentTest';
GO

Just to be clear, the following part was the bit that added the step:

EXEC sp_add_jobstep  
    @job_name = N'SqlAgentTest',  
    @step_name = N'Insert data for step 1',  
    @subsystem = N'TSQL',  
    @command = N'USE TestDB; INSERT INTO SqlAgentJobs VALUES (''SqlAgentTest'', 1, SYSDATETIME())';

Add a New Job Step

We can now use sp_add_jobstep again to add a new step to the job.

USE msdb;
EXEC sp_add_jobstep  
    @job_name = N'SqlAgentTest',  
    @step_name = N'Insert data for step 2',  
    @subsystem = N'TSQL',  
    @command = N'USE TestDB; INSERT INTO SqlAgentJobs VALUES (''SqlAgentTest'', 2, SYSDATETIME())';

This adds a second step to the job.

Update the Workflow

If you want the job to automatically progress from step 1 to step 2, you’ll need to specify that in job step 1.

The default action for any job step is to quit the job with success as soon as it succeeds. If the step fails, the default action is to quit with failure.

So as our job currently stands, the job will finish as soon as step 1 has completed (without running step 2), and the history will report that the job ran successfully.

Therefore, we need to use sp_update_jobstep to update step 1, so that it progresses to step 2 once it successfully completes.

We can also specify what to do if step 1 fails.

USE msdb;
EXEC sp_update_jobstep  
    @job_name = N'SqlAgentTest',  
    @step_id = 1,  
    @on_success_action = 3,  
    @on_fail_action = 3;

In this case I’ve specified that it should progress to the next step both if it succeeds, and also if it fails.

The values that you can provide to @on_success_action and @on_fail_action are as follows:

1	Quit with success. This is the default for @on_success_action.
2	Quit with failure.This is the default for @on_fail_action.
3	Go to next step.
4	Go to step (ID). This is where you provide the ID of a step for which you want the job to progress to.

View the Job Steps

You can use sp_help_job to get information about the SQL Server Agent jobs in the system.

You can use it with or without parameters, but to get the job step details, you need to provide the job name or ID.

We can use it to see both of the job steps that we created for the job.

Like this:

EXEC sp_help_job 
	@job_name = 'SqlAgentTest';

Here’s what the result looks like when executing it in SSMS:

Here’s an example of the code when using the ID:

EXEC sp_help_job 
	@job_id = '343D9F2A-070A-4367-BF69-4248FFF57D70';

When using the ID, you can omit the parameter name if you wish.

Example:

EXEC sp_help_job '343D9F2A-070A-4367-BF69-4248FFF57D70';

You can also use sp_help_jobstep in the same way to return just the job steps (without all the other info about the job). This accepts the job name or ID, as well as an optional step name or ID.

Full Syntax

The full syntax of sp_add_jobstep goes like this:

sp_add_jobstep [ @job_id = ] job_id | [ @job_name = ] 'job_name'
     [ , [ @step_id = ] step_id ]
     { , [ @step_name = ] 'step_name' }
     [ , [ @subsystem = ] 'subsystem' ]
     [ , [ @command = ] 'command' ]
     [ , [ @additional_parameters = ] 'parameters' ]
          [ , [ @cmdexec_success_code = ] code ]
     [ , [ @on_success_action = ] success_action ]
          [ , [ @on_success_step_id = ] success_step_id ]
          [ , [ @on_fail_action = ] fail_action ]
          [ , [ @on_fail_step_id = ] fail_step_id ]
     [ , [ @server = ] 'server' ]
     [ , [ @database_name = ] 'database' ]
     [ , [ @database_user_name = ] 'user' ]
     [ , [ @retry_attempts = ] retry_attempts ]
     [ , [ @retry_interval = ] retry_interval ]
     [ , [ @os_run_priority = ] run_priority ]
     [ , [ @output_file_name = ] 'file_name' ]
     [ , [ @flags = ] flags ]
     [ , { [ @proxy_id = ] proxy_id
         | [ @proxy_name = ] 'proxy_name' } ]

See Microsoft’s documentation for sp_add_jobstep for an explanation of each parameter, as well as the values that each accepts.

The full syntax of sp_update_jobstep goes like this:

sp_update_jobstep   
     {   [@job_id =] job_id   
       | [@job_name =] 'job_name' } ,  
     [@step_id =] step_id  
     [ , [@step_name =] 'step_name' ]  
     [ , [@subsystem =] 'subsystem' ]   
     [ , [@command =] 'command' ]  
     [ , [@additional_parameters =] 'parameters' ]  
     [ , [@cmdexec_success_code =] success_code ]  
     [ , [@on_success_action =] success_action ]   
     [ , [@on_success_step_id =] success_step_id ]  
     [ , [@on_fail_action =] fail_action ]   
     [ , [@on_fail_step_id =] fail_step_id ]  
     [ , [@server =] 'server' ]   
     [ , [@database_name =] 'database' ]  
     [ , [@database_user_name =] 'user' ]   
     [ , [@retry_attempts =] retry_attempts ]  
     [ , [@retry_interval =] retry_interval ]   
     [ , [@os_run_priority =] run_priority ]  
     [ , [@output_file_name =] 'file_name' ]   
     [ , [@flags =] flags ]  
     [ ,  {   [ @proxy_id = ] proxy_id   
            | [ @proxy_name = ] 'proxy_name' }

See Microsoft’s documentation for sp_update_jobstep for an explanation of each parameter, as well as the values that each accepts.