What is the most efficient way to count the results of a stored procedure, from another stored procedure?

Question

In a stored procedure, I need to get the count of the results of another stored procedure. Specifically, I need to know if it returns any results, or an empty set.

I could create a temp table/table variable, exec the stored procedure into it, and then run a select count on that data. But I really don't care about the data itself, all I need is the count (or presence/absence of data). I was wondering if there is a more efficient way of getting just that information.

I don't want to just copy the contents of the other stored procedure and rewrite it as a select count. The stored procedure changes too frequently for that to be workable.

Answer 1

@@ROWCOUNT

Answer 2

If you can rewrite other procedure to be a simple function that returns a resultset, you can simply select count(*) from it.

Answer 3

use an out parameter

Answer 4

Well, depending on how the stored procedures work, @@ROWCOUNT returns the # of results for ANYthing that SP will do (including updates): http://msdn.microsoft.com/en-us/library/ms187316.aspx

This will only work if the LAST thing you do in the sp is returning the rows to the client... Otherwise you're going to get the results of some other statement. Make sense?

Answer 5

I would think you could return the number of rows (using RETURN) or use an out parameter to get the value.

Answer 6

It seems that is someone else altering the other stored procedure and you need something to effectively check on the results no matter the changes to such procedure.

Create a tempt table and insert the result from that procedure in it.

Then you can perform a row count on the results. It is not the most efficient but most reliable solution if I understand correctly your problem.

Snipet:

DECLARE @res AS TABLE (
    [EmpID] [int] NOT NULL,
    [EmpName] [varchar](30) NULL,
    [MgrID] [int] NULL
)

INSERT @res 
EXEC dbo.ProcFoo

SELECT COUNT(*) FROM @res

Answer 7

Given that you don't really need to know the count, merely whether there is or isn't data for your sproc, I'd recommend something like this:

CREATE PROCEDURE subProcedure @param1, @param2, @Param3 tinyint OUTPUT
AS
BEGIN
  IF EXISTS(SELECT * FROM table1 WHERE we have something to work with)
   BEGIN
    -- The body of your sproc
    SET @Param3 = 1
   END
  ELSE
   SET @Param3 = 0
END

Now you can execute the sproc and check the value of @Param3:

DECLARE @ThereWasData tinyint
exec subProcedure 'foo', 'bar', @ThereWasData OUTPUT
IF @ThereWasData = 1 
  PRINT 'subProcedure had data'
ELSE
  PRINT 'subProcedure had NO data'