SQL SERVER 2005- How to call nested stored procedure and save the output in Temporary Table

Posted by Joggee | SQL Server 2005 | Friday 31 August 2007 11:42 am

Well, I have to create a procedure which calls nested procedures to reduce the work again and store the output of the nested stored procedure in the temporary table.
Here are the few steps taken by me to achieve this task
Suppose you have stored procedure named sp_FirstProcedure and sp_SecondProcedure

Code for sp_ FirstProcedure
Create Procedure sp_First
AS
BEGIN
   Create Table #tempTable
   ( City     varchar(255),
    EmployeeName   varchar(255)
   )
   Insert into #tempTable Values (’Alabama’,’Rana’)

   Insert into #tempTable Values (’Alabama’,’John’)

   Insert into #tempTable Values (’Alabama’,’Richard’)

   Insert into #tempTable Values (’Alabama’,’Kash’)

   Select * from #TempTable

   Drop Table #TempTable
END
GO

Output is :

firstresult.jpg

Here is the second stored procedure where I will call first procedure
By using these steps.
 

Create Procedure sp_SecondProcedure
AS

BEGIN
Create table #tempCityEmp
(
  City varchar(255),
  EmployeeName varchar(255)

)
Insert into #tempCityEmp
Exec sp_First

– here you can Manuplate your temporary table like I want to —– take only the record with Rana’s name

Select * from #tempCityEmp where EmployeeName like ‘Rana’

END
Go

Output would be
 secondoutput.jpg

This is how you call nested stored procedure and insert the out put in a temporary table.

6 Comments »

  1. Comment by stan — October 10, 2007 @ 7:51 pm

    isnt there supposed to be a way of creating and inserting into a temp table in one step?

  2. Comment by codeproject — October 11, 2007 @ 9:14 pm

    Yes, you can create and insert values in temp table but using select query not stored procedure.

    i.e.

    Select * into #temp from Table

    You can also insert the values into temp table from Stored Procedure but Temp table should created before.

    i.e.
    Insert into #tempCityEmp Exec sp_First

    Hope this is your answer.

    If not please eloborate in detail what exactly you are asking and what is the benefit to do this in one line and why ?

    Thank you very much for your comments.

    regards,

    Rana

  3. Comment by mark — April 15, 2009 @ 12:32 am

    I want to say – thank you for this!

  4. Comment by Neil — September 1, 2009 @ 12:48 pm

    Thanks – just what I was after!

  5. Comment by bhushan — November 10, 2009 @ 12:53 pm

    Hi,
    I did same but when executing procedure sp_ FirstProcedure within sp_SecondProcedure, it is returning results but it is not inserting value in the temp table #tempCityEmp. Is there any settings need to be done? Please let me know ASAP.

    Thanks,
    Bhushan

  6. Comment by mansoor — November 14, 2009 @ 8:02 pm

    If the 1st stored procedure is a union of 4 select statement.
    Can i create an aggragate query out of the 1st-procedure without createing a temp table?

RSS feed for comments on this post. TrackBack URI

Leave a comment