Call a stored procedure from another stored procedure without temp table

Calling a stored procedure from another stored procedure in Sql server?

I have stored procedure A and want to call store procedure B from A by passing a @mydate parameter. Stored procedure B will return a rowset which I can further use in procedure A.

I researched this on Google but did not find anything without using a temporary table. Is there an easy way to get this without a temp table.

I tried to get this done using stored procedure as A and a function as B easily. But want to know if I can get it done only by using stored procedures.

Answer:

Create PROCEDURE  Stored_Procedure_Name_2
  (
  @param1 int = 5  ,
  @param2 varchar(max),
  @param3 varchar(max)

 )
AS


DECLARE @Table TABLE
(
   /*TABLE DEFINITION*/
   id int,
   name varchar(max),
   address varchar(max)
)

INSERT INTO @Table
EXEC Stored_Procedure_Name_1 @param1 , @param2 = 'Raju' ,@param3 =@param3

SELECT id ,name ,address  FROM @Table

Or

You can call Stored Procedure like this inside Stored Procedure B.

CREATE PROCEDURE spA
@myDate DATETIME
AS
    EXEC spB @myDate

RETURN 0

Or

You can create table variable instead of tamp table in procedure A and execute procedure B and insert into temp table by below query.

DECLARE @T TABLE
(
TABLE DEFINITION
)
.
.
.
INSERT INTO @T
EXEC B @MYDATE.