SQL 计算存储过程返回的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14474469/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Counting the number of rows returned by stored procedure
提问by quarks
How do I count the number of rows a stored procedure would return the fastest way. Stored procedure returns rows around 100K to 1M records.
如何计算存储过程以最快方式返回的行数。存储过程返回大约 100K 到 1M 记录的行。
回答by Mahmoud Gamal
回答by H. Aghassi
You can define output variable:
您可以定义输出变量：
create procedure x
(@p1 int output)
as
select @p1 = count(*)
from Table
回答by user3612174
Create procedure procedurename
AS
Begin
Select * from Table --if you want where condition write here
End
Exec Procedurename
Select @@rowcount
回答by Irawan Soetomo
I have a similar task with a restriction that I must not alter the SP to get the count. Hence:
我有一个类似的任务，但有一个限制，即我不能改变 SP 来获得计数。因此：
sp_configure 'show advanced options', 1;
reconfigure;
go
sp_configure 'ad hoc distributed queries', 1;
reconfigure;
go
select count(*) from
openrowset('SQLOLEDB','Data Source=localhost;Trusted_Connection=yes;
Integrated Security=SSPI','exec DBNAME..SPName')
回答by Jorge Esteban De Mesa Alvarez
Another way to get the same result
获得相同结果的另一种方法
CREATE PROCEDURE NOMBRE_PROCEDIMIENTO
as
BEGIN
if EXISTS (SELECT * from NOMBRE_TABLA WHERE CONDITIONS HERE)
BEGIN
SELECT @@ROWCOUNT
END
END
回答by Lukasz Szozda
The answer is to use @@ROWCOUNTis still valid, but I would not recommend to run in directly after EXEClike on existing answer.
答案是使用@@ROWCOUNT仍然有效，但我不建议在EXEClike on existing answer之后直接运行。
SELECTstatement is not always the last statement is stored procedure or you could have multiple SELECTstatements:
SELECT语句并不总是最后一个语句是存储过程，或者您可以有多个SELECT语句：
Scenario:
设想：
CREATE PROCEDURE p
AS
BEGIN
CREATE TABLE #t(i INT);
INSERT INTO #t(i) VALUES (1),(2);
SELECT i FROM #t;
DROP TABLE IF EXISTS t;
END
EXEC p;
-- i
-- 1
-- 2
SELECT @@ROWCOUNT;
-- 0 instead of 2
One way is to use output parameter(as many as stored procedure resultset):
一种方法是使用输出参数（与存储过程结果集一样多）：
CREATE PROCEDURE p(@cnt INT OUT)
AS
BEGIN
CREATE TABLE #t(i INT);
INSERT INTO #t(i) VALUES (1),(2);
SELECT i FROM #t;
SET @cnt = @@ROWCOUNT; -- immediately after SELECT
DROP TABLE IF EXISTS t;
END
DECLARE @i INT;
EXEC p2 @cnt = @i OUT;
SELECT @i;
-- 2
