CODEWITHSUNDEEP
sqlsql-server
Adrian Smith
Adrian Smith

Reputation: 139

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

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!

Upvotes: 0

Views: 49

Answers (1)

Stuck at 1337
Stuck at 1337

Reputation: 2084

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.

Upvotes: 1

Related Questions