CODEWITHSUNDEEP
sqlsql-server-2008
quarks
quarks

Reputation: 35276

Counting the number of rows returned by stored procedure

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.

Upvotes: 26

Views: 122914

Answers (8)

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

The answer is to use @@ROWCOUNT is still valid, but I would not recommend to run in directly after EXEC like on existing answer.

SELECT statement is not always the last statement is stored procedure or you could have multiple SELECT statements:

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

db<>fiddle demo

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

db<>fiddle demo

EDIT: Using RETURN ...:

CREATE TABLE t(i INT);

CREATE PROCEDURE p2
AS
BEGIN
  INSERT INTO t(i) VALUES (1),(2);
  DECLARE @cnt INT = @@ROWCOUNT; 
   -- some other operations if needed
  RETURN @cnt;
END

DECLARE @cnt INT;
EXEC @cnt = p2;
SELECT @cnt AS cnt_rows_inserted;

db<>fiddle demo2

Upvotes: 5

jhannssyr
jhannssyr

Reputation: 1

/* prefix main result query execution follow with */

SELECT @mySProcRowcount = @@ROWCOUNT;

has been reliable with non-cloud based versions of MSSQLSERVER for many years.

not so much anymore with cloud-based of versions of MSSQLSERVER. i for one feel MS earned a 'for shame' comment for this in their rush to make all MS products cloud-based, first and foremost. in their haste, they reveal their cloud-based tools are less ready for prime time than they think.

it isn't insurmountable; it's just more work for a quality ready-for-prime-time solution on your part.

Upvotes: -2

user2203846
user2203846

Reputation: 31

So far the only thing that has worked for me is to:

a. Modify momentarily the stored procedure to dump the resulting dataset into a table. If changing the stored procedure is not an option replace Alter Procedure with Declare and remove the end, provide parameter values if not optional and execute as a query dumping the dataset into a table.

b. Script the table and drop it using SSMS.

c. Use the script to create a virtual table in the query and use Insert into with exec stored procedure to populate it.

d. Count the records.

Upvotes: 0

user3612174
user3612174

Reputation: 15

Create procedure procedurename
AS
Begin

    Select * from Table --if you want where condition write here

End

Exec Procedurename

Select @@rowcount

Upvotes: -1

Jorge Esteban De Mesa Alvarez
Jorge Esteban De Mesa Alvarez

Reputation: 53

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

Upvotes: 0

Irawan Soetomo
Irawan Soetomo

Reputation: 1325

I have a similar task with a restriction that I must not alter the SP to get the count. Hence:

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')

Upvotes: 0

H. Aghassi
H. Aghassi

Reputation: 231

You can define output variable:

create procedure x
    (@p1 int output)
as
    select @p1 = count(*) 
    from Table

Upvotes: 5

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Select @@rowcount:

SELECT @@ROWCOUNT;

After executing the stored procedure.

Upvotes: 34

Related Questions