Counting results of stored procedure

Question

I have a stored procedure returning ID, Name, Descriptions and takes no input parameters. However, I am interested in how many results do I get.

I expected something like this work:

SELECT COUNT(*) FROM EXEC MyStoredProcedure

But I get the following error in SqlServer Managment Studio: Incorrect syntax near the keyword 'EXEC'. Could you show me a little code example how can I do that?

Answer 1

This won't work. May I suggest:

exec MyStoredProcedure
select @@rowcount

Alternatively you could return the count as an output parameter

Answer 2

You need to put the logic in the stored proc and return the count from the stored proc. You do this by using the @@ROWCOUNT variable immediately after your query. This ihow it would work in MS SQL Servet at least.

Stored Proc:

CREATE PROC MyPROC
AS
DECLARE @MyCount int

...

SELECT * FROM MyTable WHERE ...

SELECT @MyCount = @@ROWCOUNT

...

return @MyCOunt

Calling code:

DECLARE @MyCount int

EXEC @MyCount = EXEC MyProc