How can I count the number of rows in a table using a procedure in SQL Server

Question

I can't seem to crack this one, I will appreciate your assistance!

I'm willing to write a stored procedure that receives a table name (string) and if the table exists, then the procedure should print the number of rows in the table, otherwise, it should print "-1".

I've tried the next code both it keeps printing "-1" even though the procedure is being applied to existing tables:

CREATE PROCEDURE test_pro (@Table_Name varchar(50))

AS

BEGIN

IF OBJECT_ID ('@Table_Name') IS NOT NULL

SELECT COUNT(*)

FROM CONCAT('dbo.', @Table_Name) AS num_rows

ELSE PRINT '-1'

END

 
EXEC test_pro training_kehila

 
-- Dropping the procedure
IF OBJECT_ID ('test_pro') IS NOT NULL
DROP PROCEDURE test_pro

Thanks!

You might supply training_kehila to the stored procedure but it will always be checking if a table named @Table_Name exists. To measure the actual row count you'll need dynamic SQL. — AlwaysLearning
– AlwaysLearning, Commented Nov 8, 2022 at 12:24

Stuck at 1337 · Accepted Answer · 2022-11-08 12:53:26Z

1

Well:

OBJECT_ID ('@Table_Name')

Should be:

OBJECT_ID (@Table_Name)

But that won't help, because you still can't say FROM @Table_Name.

This is the wrong approach anyway (counting all of the rows in the table). Number of rows in a table is accessible via a simple call to the metadata, which will make a massive difference in performance as your tables get bigger, and which doesn't require dynamic SQL at all.

CREATE PROCEDURE dbo.test_pro 
   @Table_Name nvarchar(128) -- not varchar(50)!
AS
BEGIN
  SELECT NumberOfRows = COALESCE(c, -1)
  FROM 
  (
    SELECT c = SUM(rows)
    FROM sys.partitions
    WHERE object_id = OBJECT_ID(CONCAT(N'dbo.', QUOTENAME(@Table_Name)))
      AND index_id IN (0,1)
  ) AS s;
END

And then to call it:

EXEC dbo.test_pro @Table_Name = N'training_kehila';

Working example in this fiddle.

edited Nov 8, 2022 at 12:53

answered Nov 8, 2022 at 12:40

Stuck at 1337

2,0941 gold badge6 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I count the number of rows in a table using a procedure in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related