how to call stored procedure and stored return data to the temporary table in sql server?

Question

i write a stored procedure on sql server database

and i want to call another stored procedure to returning a table for me and store it to temporary table

Answer 1

For example, try this:

insert into #Temp1
exec MyProc2

where MyProc2 is stored procedure that returns the table (e.g. it contains a select statement), #Temp1 — the temporary table (defined before this call), that has the corresponding columns.

Answer 2

In addition to Viorel's post: On my web site have an article How to Share Data Between Stored Procedures, where I discuss a multitude of options.

Answer 3

Hi @reza yari

Have you considered using Table-valued UDF instead of stored procedure?

Here is a sample from Christian Loris's answer in this similar thread:

CREATE FUNCTION CustomersByRegion 
(  
    @RegionID int  
)
RETURNS TABLE 
AS
RETURN 
  SELECT *
  FROM customers
  WHERE RegionID = @RegionID
GO

You can then call this function to get what your results a such:

SELECT * FROM CustomersbyRegion(1)

Or to do a SELECT INTO:

SELECT * INTO #temp_table FROM CustomersbyRegion(1)

If you still need a stored procedure, then wrap the function as such:

CREATE PROCEDURE uspCustomersByRegion 
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.