Calling SP from within SP with shared input parameters

  • I have two stored procedures:

    SP1 requires inputs @input1, @input2 and @input3

    SP2 requires inputs @Input2, @Input3 and @ Input4 (notice that 2 of the inputs are the same as SP1)

    SP2 is to be called within SP1 which is defied as:

    Alter Procedure dbo.SP1 (@Input1, @Input2, @Input3, @Input4)

    ....... (SP1 code) ....

    EXEC dbo.SP2

    ........ (SP2 code) ....

    My question is: Can I call SP2 without any input parameters as the required parameters are already available with SP1?

    Appreciate any suggestions. Thanks in advance.

    sg2000

  • i don't think you can directly (but i could be wrong), but more importantly, why would you want to? in other words, if you pass the parameters from sp1 into sp2, you're virtually guaranteeing that sp2 is getting the parameters you want it to get each and every time. i wouldn't take any chances... pass the parameters from sp1 into sp2.

    just my $0.02...

  • Lenny:

    Thanks for the quick response. Yes, I just specified the input parameters for SP2 and it works OK, with the exception of the Return value. It seems that the return value is that of the SP1 and not SP2, though SP2 is the one which executed last. Any idea on how to get the Return Value of SP2?

    sg2000

  • there are always 1,001 ways to do it; here are 3:

    1. capture the return value from sp2 and return it when sp1 finishes (but then you lose the return from SP1)

    2. SELECT your return values when sp1 finishes

    3. create output parameters in sp1 that return both sp1 and sp2 values

  • Lenny, thanks for the suggestions. Solution # 1 suits my application. Thanks again.

    sg2000

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply