[sql-server-2008] How to call a stored procedure (with parameters) from another stored procedure without temp table

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.

P.S.: 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.

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

and you continue operation.

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

CREATE PROCEDURE spA
@myDate DATETIME
AS
    EXEC spB @myDate

RETURN 0

 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  

You can call a stored procedure from another stored procedure by using the EXECUTE command.

Say your procedure is X. Then in X you can use

EXECUTE PROCEDURE Y () RETURNING_VALUES RESULT;"

You can just call the Execute command.

EXEC spDoSomthing @myDate

Edit:

Since you want to return data..that's a little harder. You can use user defined functions instead that return data.