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

Posted on 2014-08-04
Last Modified: 2014-08-04
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
Question by:johnnyg123
    LVL 39

    Expert Comment

    by:Kyle Abrahams

    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.

    Author Comment

    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
    LVL 23

    Expert Comment

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

    DECLARE @MyVar INT = 0;
    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


    Author Comment

    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
    LVL 23

    Accepted Solution

    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.

    Author Closing Comment

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now