asked on

Select row count with variable table name in SQL Server

Dear Experts,

I'm trying to count the number of rows in a table whose name is a variable; for exmple @TableName

I'm trying to achieve something like this:

DECLARE @cnt int
SELECT @cnt = COUNT(*) FROM @Table WHERE Word='word'

Could anybody advise of the best way to do this?

Thanks

Nick

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nkewney

ASKER

Thanks for the response:

This gets

Msg 156, Level 15, State 1, Procedure procSuggestionInsert, Line 26
Incorrect syntax near the keyword 'EXEC'.

Steve Krile

If I may...

To do what you want, you can use this approach:
http://www.sommarskog.se/dynamic_sql.html

The option offered above creating a SQL select inline is technically OK, but kind of fraught with peril.

May I suggest a different approach.

Pass in the name of your table, then write a bunch of select statements with If's. This manual approach may seem crazy and inefficient, but you only have to do it once per table, and it is MUCH safer.

ex:

CREATE procedure table_count(@TableName varchar(100))

SET NOCOUNT ON

IF @TableName = 'SomeTable'
BEGIN
SELECT Count(SomeTableID) FROM SomeTable
END

IF @TableName = 'SomeOtherTable'
BEGIN
SELECT Count(SomeOtherTableID) FROM SomeOtherTable
END

SET NOCOUNT OFF

I find this approach is nice because I often start with a simple premise....I just want to know how many rows.......but find over time that I want to count all rows of most tables, but that one table, I only want "active" records, and that other table, I only want rows with Sum values...etc etc etc.

momi_sabag

try

DECARE @cnt int, @tbl varchar(100), @col varchar(100), @word varchar(100), @sql varchar(1000)

SET @sql = 'SELECT @Count=COUNT(*) FROM [' + @tbl + '] WHERE [' + @col + '] = ''' + @word + ''''

EXECUTE sp_executesql
@SQL
,N'@Count int OUTPUT'
,@cnt = @Count OUTPUT
0