How to Pass parameters from one step to another step in SQL JOB

Aniket Narvankar

My Requirement is such that I need to do it using Job in SQL Server only. I have created a job with 7 steps. I need help in how to pass paramters from one step to another in SQL Server Job. I am giving queries of first two steps.

1.Here is query of step 1

declare @lastRunStatus nvarchar(4)
declare @bkp nvarchar(10)
declare @bky nvarchar(10)
declare @bkpd varchar(10)
declare @bkyr varchar(10)
declare @bkpbky varchar(6)

select @lastRunStatus = LastRunStatus,@bkp = BookingPeriod,@bky = BookingYear
from tbl1 where Roll_Forward_Type_Name='AY_Allocation'

select @bkpd = Cast(BookingPeriod as varchar(10)),@bkyr = Cast(BookingYear as varchar(10)) from tbl2 where DomainCode='PSTIBR' and closedFlag='N'

@bkpd,@bky,@bkpbky,@bkp,@bkyr

I want to pass this parameters from step 1 to step 2.

2.Here is the query of step 2

Select * from tbl_Allocation_Status_Test where bky=@bky and bkp=@bkp and bkpbky=@bkpbky and bkyr=@bkyr and bkpd=@bkpd

There are total 7 steps in job and in that steps also I need to pass parameters from one step to another. Please help me understanding on how to pass parameters from one step to another. I need to go with sql job as per company's requirement and limitations.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tuhin Paul3y ago
Hello Aniket,

To pass parameters from one step to another in a SQL Server Job, you can make use of the "Output" option available in the "Advanced" tab of each job step. This option allows you to specify one or more output files that can be used to pass information from one step to the next. try the following steps:

step1: In the first step of the job, declare and set the values for the parameters that you want to pass to the next step.

step2: In the same step, add an "Output" option to the step by following these steps:

a. Right-click on the step and select "Properties".

b. Click on the "Advanced" tab.

c. Under the "Output file" section, select "True" for the "Include step output in history" option.

d. Enter the file path and name for the output file. This file will be created by the first step and will contain the parameter values that you want to pass to the next step.

step3: In the third step of the job, add a reference to the output file that was created in the first step by following these steps:

a. Right-click on the step and select "Properties".

b. Click on the "Advanced" tab.

c. Under the "Input file" section, select "True" for the "Include step output in history" option.

d. Enter the file path and name for the output file that was created by the first step.

step4: In the fourth step, use the parameter values from the output file in your query by referencing the file name and column names. For example:
```
Select * from tbl_Allocation_Status_Test where bky=$(ESCAPE_SQUOTE(STEP1_OUTPUT.bky)) and bkp=$(ESCAPE_SQUOTE(STEP1_OUTPUT.bkp)) and bkpbky=$(ESCAPE_SQUOTE(STEP1_OUTPUT.bkpbky)) and bkyr=$(ESCAPE_SQUOTE(STEP1_OUTPUT.bkyr)) and bkpd=$(ESCAPE_SQUOTE(STEP1_OUTPUT.bkpd))
```
Note that the "STEP1_OUTPUT" part of the query refers to the name of the step that created the output file, and the column names refer to the names of the variables that were declared and set in the first step.
Vishal Yelve3y ago
Hi Aniket,

https://www.mssqltips.com/sqlservertip/5731/how-to-pass-data-between-sql-server-agent-job-steps/

https://dba.stackexchange.com/questions/211883/whats-the-best-way-to-pass-a-variable-valuable-between-steps-in-a-sql-server-jo