CODEWITHSUNDEEP
sqlt-sqlstored-procedures
pistacchio
pistacchio

Reputation: 58863

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

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

Upvotes: 34

Views: 46899

Answers (6)

Spikeh
Spikeh

Reputation: 3695

Interestingly, this seems to work fine if the EXEC'ed sproc has only a simple SELECT in it (with SET NOCOUNT ON), but as soon as you add any other T-SQL statements (IF, DECLARE etc), then you get a null rowset. I can confirm this as the case in Microsoft SQL Server 2019 (RTM-CU19) (KB5023049) - 15.0.4298.1 (X64) (running in a Linux container).

However, the most consistent way is to declare a VIEW for the end result that you want to SELECT from, and SELECT from that VIEW instead.

Upvotes: 0

Dance-Henry
Dance-Henry

Reputation: 953

First, you CANNOT RETURN a recordset by stored procedure. By return, a stored procedure can only return integers.

You mentioned SELECT statement, which is a DQL and just for display purpose.

The way you can do to work around this issue is that you can assign the recordset to a global temporary table which can also be accessed within the outer stored procedure.

Upvotes: 0

user2355869
user2355869

Reputation: 21

You can do this with an output variable in the stored proc. For example:

CREATE PROCEDURE sp_HelloWorld   @MyReturnValue int OUT
AS
SELECT @MyReturnValue = 100
Return @MyReturnValue

To call this stored proc, do the following:

DECLARE @TestReturnVal int 

EXEC sp_HelloWorld @TestReturnVal output
SELECT @TestReturnVal

Upvotes: 2

dretzlaff17
dretzlaff17

Reputation: 1719

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

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

Upvotes: 3

Ben Hoffstein
Ben Hoffstein

Reputation: 103325

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

There are some other techniques listed here.

Upvotes: 39

Kendrick
Kendrick

Reputation: 3787

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
    ...

Upvotes: 4

Related Questions