[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Select row count with variable table name in SQL Server

Posted on 2008-10-13
4
Medium Priority
?
2,623 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 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

873 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