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

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:

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.
johnnyg123Author Commented:
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
nemws1Database AdministratorCommented:
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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johnnyg123Author Commented:
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
nemws1Database AdministratorCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnnyg123Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.