Solved

Select row count with variable table name in SQL Server

Posted on 2008-10-13
4
2,570 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Read about achieving the basic levels of HRIS security in the workplace.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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

12 Experts available now in Live!

Get 1:1 Help Now