Attempting to access results / result set of an SQL EXQL server statement

Question

Say I saved proc 1, which returns some data. how can I execute this stored proc and set some parameters in another stored procedure based on the results of this stored procedure.

For example: Table:

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

Saved Proc 1:

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

Saved 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 this is what I want to do, set the score based on the last set of results.

I cannot change saved proc 1

Answer 1

you can catch the results of your first stored procedure 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...

- @@ROWCOUNT, proc. , select ...

DECLARE @Count int

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

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

StoredProc1, - , @@ROWCOUNT, reset select StoredProc1.

Answer 2

SP1, :

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 3

( ), . , , , ( ) .