Stored Procedure Use Results from another?

  • Is there a way to call a Stored Procedure(1) from within another stored procedure(2) and use the produced from stored procedure(2) in stored procedure(1?).

    I am trying to convert 9 nested queries from Access to Stored Procedures and hopefully alleviate a migraine.


    Thanks for any input.


  • Local temporary table.

  • Just to add to Allan, you can nest stored procedures up to 32 levels.

    Frank Kalis
    Microsoft SQL Server MVP
    My blog:[/url]

  • If you are using SQL 2K you may want to examine using a table returning or inline user-defined function.  You can almost look at these like a parameterized view.  It can get messy when nesting sp's.

  • I have explored the use of #Temp, doing an insert on that table takes about 4 secs. for about 11000 rows.  However, when I attempt to query up any data that is in #temp from another SP I get no results.

    The nested SPs may be an option, but I can not find any examples of how to do this.  Everything that I find just reiterates what Books online says.  (Not much).

    Can someone post an example of a nesting Stored Procedures or point me in the right direction?



  • use pubs


    alter proc sp1 as

    select * from #te456 where state = 'ca'


    create proc sp2 as

    select * into #te456 from authors

    exec sp1

    drop table #te456


    exec sp2

  • Thanks wz700, I will try that.

Viewing 7 posts - 1 through 7 (of 7 total)

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