Solved

Select row count with variable table name in SQL Server

Posted on 2008-10-13
4
2,605 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:nkewney
4 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 22702418
DECARE @cnt int, @tbl varchar(100), @col varchar(100), @word varchar(100), @sql varchar(1000)

SET @sql = 'SELECT COUNT(*) FROM [' + @tbl + '] WHERE [' + @col + '] = ''' + @word + ''''
SET @cnt = EXEC(@sql)
0
 
LVL 1

Author Comment

by:nkewney
ID: 22702449
Thanks for the response:

This gets

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

Expert Comment

by:Steve Krile
ID: 22703004
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.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22703153
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
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question