TSQL: Call a stored procedure from another stored procedure and read the result

Question

Hi,

I have a stored procedure that, ending with a SELECT, returns a recordset. I can call it within anoher stored procedure like this:

EXEC procedure @param

How to get the returning recordset? Thanks

Answer 1

AFAIK, you can't. What you probably want to do is use a function for your first (or both) procedures. Functions can only return one thing, but they can return a table. Stored procedures can return multiple results, but not to other functions/stored procedures.

e.g.:

CREATE FUNCTION [dbo].[fn_GetSubordinates] (
    @sPersonID VARCHAR(10),
    @nLevels INT
)
RETURNS @tblSubordinates TABLE
(
    Person_Id VARCHAR(10),
    Surname char(25),
    Firstname char(25)
)
AS
BEGIN
    ...

Answer 2

You can create a temp table and then use INSERT INTO #MyTable EXEC procedure @param.

There are some other techniques listed here.

Answer 3

If you are using SQL Server 2008, I would recommend returning a Table-Valued Parameter.

http://msdn.microsoft.com/en-us/library/bb510489.aspx