SQL SERVER – HOW TO UPDATE SQL JOB STEP SCRIPT DYNAMICALLY

Generally we create SQL Server Jobs to execute some SQL scripts, SSIS packages or power shell commands when user calls job manually or call automatically by Scheduler. Sometimes we need to change SQL server Job steps script dynamically based on some dynamic values. Now let’s see, how to change SQL server Job steps command dynamically

To show this, I have taken example of SQL Job step with T-SQL Command. Before this I have created one simple stored procedure in ADVENTUREWORKSDW2012 DB to insert values into DIMPRODUCT table.

USE [ADVENTUREWORKSDW2012]

GO

CREATE PROC DBO.INSERTINTODIMPRODUCT

(

@PRODUCTNAME VARCHAR(100),@COLOR VARCHAR(100)

)

AS

BEGIN

                     INSERT INTO [ADVENTUREWORKSDW2012].[DBO].[DIMPRODUCT]

                     (

                     ENGLISHPRODUCTNAME,SPANISHPRODUCTNAME,FRENCHPRODUCTNAME,FINISHEDGOODSFLAG,COLOR,STARTDATE

                     )

                     SELECT @PRODUCTNAME,@PRODUCTNAME,@PRODUCTNAME,1,@COLOR,GETDATE()

 END

We can call above Stored procedure by below 

EXEC INSERTINTODIMPRODUCT 'Pen',’Blue'

So same script i am calling from SQL Server Agent Job by creating Job name Update Job Step and Step Insert values dynamically to insert data dynamically

Go to SQL Server Agent in your Database server -->Job and right click and select New Job

 Give name

Next click on Steps  and give name for step and select type of execution as T-SQL . Choose your database to execute your command and  write your Script in command window And click on OK  and close the Job windows by saving. Now your job is created.

Next go to newly created job and right click and click on Start Job at Step . After completion of job just run the below query 

SELECT TOP 10 [PRODUCTKEY],

      ENGLISHPRODUCTNAME,SPANISHPRODUCTNAME,FRENCHPRODUCTNAME,FINISHEDGOODSFLAG,COLOR,*

  FROM [ADVENTUREWORKSDW2012].[DBO].[DIMPRODUCT]

  ORDER BY 1 DESC

Out Put will be like below 

Now if we want to change SQL script in step1 , we can update script by using sys stored procedure  SP_UPDATE_JOBSTEP. 

USE MSDB

 GO

DECLARE @JOBNAME VARCHAR(100)='UPDATE JOB STEP'

              DECLARE @PRODUCTNAME VARCHAR(100)='Ball'

              DECLARE @COLOR VARCHAR(100)='White'

              DECLARE @STEP_ID INT=1

              DECLARE @CMD VARCHAR(100)='EXEC INSERTINTODIMPRODUCT ' + @PRODUCTNAME +',' + @COLOR 

              EXEC MSDB.DBO.SP_UPDATE_JOBSTEP @JOB_NAME = @JOBNAME

              ,@STEP_ID = @STEP_ID

              ,@COMMAND = @CMD

Now if we open Step1 script in Job, i command script will change like below 

Run the job again and see results in DipProduct , data will insert like below