Solved

Select row count with variable table name in SQL Server

Posted on 2008-10-13
4
2,580 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 wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

920 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now