Trying to access the results/result-set of a SQL server EXEC statement

Question

Say I've got stored proc 1, that returns some data. how can I execute that stored proc and set some parameters within another stored proc based on the results of that stored proc.

For example: Table:

UserInfo (UserID [int], Name [varchar], DateOfBirth [datetime] )

Stored Proc 1:

CREATE Procedure StoredProc1 
    @Name [varchar] 
AS
    SELECT UserID, Name, DateOfBirth FROM UserInfo WHERE Name = @Name

Stored Proc 2:

CREATE Procedure StoredProc2 
    @AmountOfNames [int] OUT

SELECT @AmountOfNames=COUNT(EXEC StoredProc1 @Name='Irwin')

I know that Stored Proc 2 is not how it should be built but that is what I want to do, set the count based on the last set of results.

I can't modify stored proc 1

Answer 1

If you know the schema of the return of SP1, you can do this:

CREATE SP2
AS
BEGIN

CREATE TABLE #Results1 (
    UserID   VARCHAR(50),
    Name     VARCHAR(100),
    DateOfBirth   DATETIME
)

INSERT INTO #Results1
EXEC sp1 @Param1, @Param2

SELECT COUNT(UserID) FROM #Results1

END

Answer 2

you can catch the results of your first stored proc in a temp table or table variable...

DECLARE @MyResults TABLE(UserID int, Name varchar(30), DateOfBirth datetime)

INSERT  @MyResults
EXEC    StoredProc1 'Scott'

-- do what you want with the table here...

Or - you can use the @@ROWCOUNT variable to grab the count of the results from your first proc. This may or may not work depending on what comes after the select statement in your first stored proc...

DECLARE @Count int

EXEC StoredProc1 'Scott'
SET  @Count = @@ROWCOUNT

-- do whatever you want with the count here...

If you don't have control over StoredProc1, the temp table might be your best option since you can't guarantee the results of @@ROWCOUNT getting reset by code after the select statement in your StoredProc1 proc.

Answer 3

You've got good answers already (and have accepted one), but you may still be interested in How to Share Data Between Stored Procedures. Lots of information about different methods of inter-procedure communication, how they work, and why you might (or might not) choose to use each one.