25

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.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
SQLNewBee
  • 251
  • 1
  • 3
  • 3
  • Try http://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure - your exact question is covered there. – dash Dec 07 '11 at 20:35

5 Answers5

17
 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
Arun CM
  • 3,345
  • 2
  • 29
  • 35
9

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.

Ed B
  • 6,028
  • 3
  • 26
  • 35
5

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

CREATE PROCEDURE spA
@myDate DATETIME
AS
    EXEC spB @myDate

RETURN 0
Nagu_R
  • 87
  • 1
  • 12
1

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.

Amir Keshavarz
  • 3,050
  • 1
  • 19
  • 26
-2

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;"
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Eosphorus
  • 312
  • 3
  • 10
  • 19
  • If you post code, XML or data samples, **PLEASE** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Dec 07 '11 at 20:55