Counting the number of rows returned by stored procedure

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

Select @@rowcount:

SELECT @@ROWCOUNT;

After executing the stored procedure.

You can define output variable:

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