none
Stored Procedures – pass parameter values from the main procedure to the nested procedure

    Question

  • I would like to pass parameters between two stored procedures but I seem to be somehow stuck. Most of the posts that I have seen are about passing parameters from nested procedure to the main procedure, but my requirement is passing parameters from the main procedure to the nested. For example,

    Create procedure usp_main_procedure

    @parameter1 varchar(10)

    @parameter2 varchar(50)

    With recompile

    as

    begin

    exec usp_nested_procedure (pass main procedure parameters here)

    end

    Note that the nested procedure has the same number of parameters and parameter definitions as the main procedure. Could someone please help me on how I can pass parameter values from the main procedure to the nested procedure.

    Many thanks,


    Mpumelelo

    • Edited by Mpumelelo S Tuesday, April 08, 2014 10:51 AM
    Tuesday, April 08, 2014 10:48 AM

Answers

  • HI

    Try this

    Create procedure usp_main_procedure
    @parameter1 varchar(10)
    @parameter2 varchar(50)
    With recompile
    as
    begin
    exec usp_nested_procedure @parameter1,@parameter2
    end

    Note that sequence of parameters should be same as it is defined in nested store proc

    Ref 

    http://www.devguru.com/technologies/t-sql/7132

    Mark as answer if you find it useful


    Shridhar J Joshi Thanks a lot


    Tuesday, April 08, 2014 10:53 AM

All replies

  • HI

    Try this

    Create procedure usp_main_procedure
    @parameter1 varchar(10)
    @parameter2 varchar(50)
    With recompile
    as
    begin
    exec usp_nested_procedure @parameter1,@parameter2
    end

    Note that sequence of parameters should be same as it is defined in nested store proc

    Ref 

    http://www.devguru.com/technologies/t-sql/7132

    Mark as answer if you find it useful


    Shridhar J Joshi Thanks a lot


    Tuesday, April 08, 2014 10:53 AM
  • What errors are you getting?

    create procedure sp2
    @par1 int
    as
    print @par1

    create procedure sp1
    @spid int
    as
    print @spid
    select @spid=@spid+1
    exec sp2 @par1 =@spid


    exec sp1 @@spid
    415
    416


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, April 08, 2014 10:55 AM
  • As other's suggested, you can pass the parameters to nested procedures as well.

    But, I would like to ask you, Is that "With Recompile" a conscious call? Are you seeing any parameter sniffing problem in your procedure? If no reason, then you may remove "with recompile".

    Tuesday, April 08, 2014 11:01 AM
  • Thank you Shridhar and Uri. I will test your suggested solutions shortly. I will come back with the results.

    Latheesh – with recompile is very important for the kind of business logic that I am trying to address.

    Kind regards,

    Mpumelelo 


    Mpumelelo

    Tuesday, April 08, 2014 11:10 AM