Use stored procedure output parameter in one sql job step in subsequent sql job step

johnnyg123 used Ask the Experts™
I have 2 sql stored procedures

one that passes a file name as an output parameter

the other that takes in a file name as an input parameter and performs an ftp

I would like to write (and then schedule) a job that would execute the first stored proc and use the output from the first stored proc as the input value to the other stored proc that would be executed another job step

Is this possible (pass output variable value to subsequent job step)????

From a reusability and error processing stand point,  trying to avoid calling the second stored proc from within the first one
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kyle AbrahamsSenior .Net Developer


DECLARE @MyVar <dataType>
EXEC SP_1  <OtherParamters...>, @MyVar OUTPUT
Exec SP_2 @myVar

It also might be worth tying the 2 together in a 3rd procedure, and then just execing that procedure from a job.


Is that putting this is in a single step?

I was hoping to separate into separate steps to allow error processing to be done for each stored proc

For example if, error occurred trying to generate file name
Nem SchlechtIT Supervisor
Top Expert 2009

Those are separate steps.  You can put some logic in there if you want:

EXEC SP_1  <OtherParamters...>, @MyVar OUTPUT;
IF (@MyVar <> 1)
    PRINT 'Got the wrong result from SP_1 - should be 1, but got: ' + CAST(@MyVar AS VARCHAR(10));
    -- We got the right code, so we can execute this:
    EXEC SP_2 @MyVar;

Open in new window

Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!


sorry...I think I am missing something

This looks to be a single stored proc  which is calling both procs

I was looking to put it in a sql server agent job with 2 separate steps.  first step executes first proc which has output parameter value.  Second step executes second proc (with file name as input from value of output parameter value from result of step 1)

(Please see attached screen shot)   screen shot
IT Supervisor
Top Expert 2009
I'm sorry - I read your original question more carefully.  I do *NOT* think you can do this using a simple variable.  Each step is executed as a separate batch on the SQL server.  You could use either my or Kyle's code in a single job step, but not across multiple job steps.  I confirmed this just now with an agent job:

Step 1:
   DECLARE @somevar INT = 1234;
Step 2:
   SELECT @somevar;

Generated an error on step 2 saying @somevar was undeclared.

I don't know why you want to insist that this be done in separate steps.  You could have one step call both stored procedures and pass the output var easily.

If you insist on doing this, then you'll have to do something like creating either a table in one of your databases or a global temporary table (CREATE TABLE ##some_table) and write the output of the first step into this table and then read it in the second step.  If this is only run once per day, then no problem.  If it might be run twice at the same time, then this won't work because of possible concurrency issues.


Appreciate the posts

I was just curious if it was possible to pass (wasn't a case of being insistent :-) )

Since only running once a day and already had a configuration table, so ended up following your suggestion and just writing out file path to table in first proc and reading value from table in second one

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial