• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2994
  • Last Modified:

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
  • 3
  • 2
1 Solution
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
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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
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.
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now