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

2
votes

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.

9
sql-servertsql

9 Answers

3
votes

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?

1
votes

@@ROWCOUNT

1
votes

use an out parameter

1
votes

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

0
votes

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

0
votes

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
0
votes

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'
0
votes

I think you should do something like this:

Create  Procedure   [dbo].[GetResult]   (
    @RowCount   BigInt  =   -1  Output
)   As  Begin

    /*
        You can do whatever else you should do here.
    */

    Select  @RowCount   =   Count_Big(*)
        From    dbo.SomeLargeOrSmallTable
        Where   SomeColumn  =   'Somefilters'
        ;

    /*
        You can do whatever else you should do here.
    */

    --Reporting how your procedure has done the statements. It's just a sample to show you how to work with the procedures. There are many ways for doing these things.
    Return  @@Error;

End;

After writing that you can get the output result like this:

Declare @RowCount   BigInt
,   @Result     Int
;

Execute @Result =   [dbo].[GetResult]   @RowCount   Out

Select  @RowCount
,   @Result
;

Cheers

0
votes
  create proc test
    as
    begin
     select top 10 * from customers
    end
    go


    create proc test2 (@n int out)
    as
    begin
    exec test
    set @n = @@rowcount
    --print @n
    end
    go

    declare @n1 int =0

    exec test2 @n1 out
    print @n1
    --output result: 10