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
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/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.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • 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?

    Thanks

     

  • use pubs

    go

    alter proc sp1 as

    select * from #te456 where state = 'ca'

    go

    create proc sp2 as

    select * into #te456 from authors

    exec sp1

    drop table #te456

    go

    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