?
Solved

Select row count with variable table name in SQL Server

Posted on 2008-10-13
4
Medium Priority
?
2,618 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Viewers will learn how the fundamental information of how to create a table.
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…

752 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