CODEWITHSUNDEEP
sqlsql-server-2008
Yagnesh.Dixit
Yagnesh.Dixit

Reputation: 318

Stored procedure to find number of rows in a table

In a stored procedure I pass a table name as the input variable.

I want to return the number of rows of this table with that stored procedure.

I tried something like this but it did not work:

declare @maxRowCount bigint
exec('set '+ @maxRowCount + ' =(select COUNT(1) from ' + @tableName + ')')

This is SQL Server 2008.

Upvotes: 0

Views: 5048

Answers (4)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can try this instead.

declare @maxRowCount bigint(5)
exec('SELECT COUNT(*) INTO @maxRowCount FROM ' + @tableName)

Upvotes: 0

Hitesh
Hitesh

Reputation: 3498

You can try this

CREATE PROCEDURE dbo.sp_selectcount
  @tablename NVARCHAR(200)
AS

DECLARE @cmd NVARCHAR (255)
SET @cmd = 'SELECT count(*) from ' + @tablename
EXEC sp_executesql @cmd

Upvotes: 2

Josien
Josien

Reputation: 13867

The following example should give you something to work with.

-- fully qualify your table name (this is probably an input value in your sproc?)
-- please note that I use system view master.sys.tables as an example table here
DECLARE @tablename NVARCHAR(MAX) = N'[master].[sys].[tables]';

-- build the sql statement that you will execute
DECLARE @sql NVARCHAR(MAX) = N'SELECT COUNT(*) FROM ' + @tablename;

-- create a variable to hold the number of rows later on
DECLARE @nrofrows BIGINT;

-- create a temp table to store the result of executing the sql statement
CREATE TABLE #temp (NrOfRows BIGINT);

-- insert the result of the execution of the sql statement into the temp table
INSERT INTO #temp
EXECUTE(@sql);

-- extract the number of rows from the temp table
SET @nrofrows = (SELECT NrOfRows FROM #temp);

-- check the result so you can test!
PRINT @nrofrows;

If you want good background information on dynamic SQL, check out Erland Sommarskogs article The Curse and Blessings of Dynamic SQL.

Upvotes: 1

Raging Bull
Raging Bull

Reputation: 18737

You should remove the quotes around @maxRowCount.

Try this:

declare @maxRowCount bigint
exec('set @maxRowCount =(select COUNT(*) from ' + @tableName + ')')

OR

exec('SELECT @maxRowCount = COUNT(*) from ' + @tableName)

Analysis:

With the query you tried, it will execute:

set blablabla = (select count(1) from MyTable)

By removing the quotes:

set @maxRowCount = (select count(*) from MyTable)

Upvotes: 0

Related Questions