从另一个存储过程计算存储过程结果的最有效方法是什么？

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.

我可以创建一个临时表/表变量,将存储过程放入其中,然后对该数据运行select count。但我真的不关心数据本身,我需要的只是计数(或数据的存在/不存在)。我想知道是否有更有效的方法来获取这些信息。

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 个解决方案

#1

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

好吧,根据存储过程的工作方式,@@ ROWCOUNT返回SP将执行的任何内容的结果#(包括更新):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?

只有当您在sp中执行的最后一项操作将行返回到客户端时,这才有效...否则您将获得其他语句的结果。合理?

#2

@@ROWCOUNT

#3

use an out parameter

使用out参数

#4

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

我认为你可以返回行数(使用RETURN)或使用out参数来获取值。

#5

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

如果您可以将其他过程重写为返回结果集的简单函数,则只需从中选择count(*)即可。

#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

#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:

鉴于你真的不需要知道计数,只是你的sproc是否存在数据,我建议这样:

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:

现在你可以执行sproc并检查@Param3的值:

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

#8

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

#9

  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

#1