Call Sp inside a SP and count the number of rows returned by the SP

[Kit Fisto]

Hi guys. I have a problem. I need to call a Stored Procedure inside a Stored Procedure and count the number of rows returned by the second SP. Something like this: COUNT(EXEC My_SP 1,1,1,1). That second SP returns a query with a x number or rows. Thanks in advance guys.

[Goutam Patra]

Declare your second SP like

CREATE PROCEDURE My_SP
@Para1 AS INTEGER,
@Para2 AS INTEGER,
@Para3 AS INTEGER,
@RecordCount AS INTEGER OUTPUT
AS
SET @RecordCount = (SELECT COUNT(*) FROM MYTABLE)
GO

In your first SP Call this like

DECLARE @RecordCount AS INTEGER
EXEC My_SP 1, 1, 1, RecordCount OUTPUT

Thats it

[Kit Fisto]

Thanks man. Thats exactly what i was looking for.