Counting results of stored procedure

Hi,

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?

From stackoverflow

• Write a new stored procedure which does a count for you.

• This won't work. May I suggest:

  exec MyStoredProcedure
  select @@rowcount
  

  Alternatively you could return the count as an output parameter

  Sem Dendoncker : what if you have set NOCOUNT to on? will this still work?

  Sem Dendoncker : sorry my bad: http://msdn.microsoft.com/en-us/library/ms189837.aspx. @@ROWCOUNT keeps working

• 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
  

  Sem Dendoncker : ok this will work but then you can also use SELECT COUNT(*) FROM Table. I think he still wants his data ... if not he should use COUNT ...

• SELECT @@ROWCOUNT