We help IT Professionals succeed at work.

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

3,761 Views
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
Comment
Watch Question

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
yep:

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

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
Nem SchlechtIT Supervisor
CERTIFIED EXPERT
Top Expert 2009

Commented:
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)
BEGIN
    PRINT 'Got the wrong result from SP_1 - should be 1, but got: ' + CAST(@MyVar AS VARCHAR(10));
END
ELSE
BEGIN
    -- We got the right code, so we can execute this:
    EXEC SP_2 @MyVar;
END

Open in new window

Author

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
IT Supervisor
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.