July 29, 2008 at 8:24 am
Hi,
This query works fine:
SET @cnt = ( SELECT COUNT(*) FROM myTable )
How do I do this if I don't know the tablename during designtime?
For example, I have three tables named myTable1, myTable2 and myTable3
I would like this:
SET @nr = '2'
SET @cnt = ( SELECT COUNT(*) FROM 'myTable'+@nr )
Obviously this doesn't work.... what should I do?
EXEC('SELECT COUNT(*) FROM myTable'+@nr) should work, but how do I get the return value?
Thanks,
Ray
July 29, 2008 at 8:48 am
here is a quick example that can get you started:
declare @sql varchar(150), @rec_count int
--hold the temp rec count for the dynamic query into a temp table of the procedure
create table #tmpcount(rec_count int)
--setup the dynamic sql and execute
set @sql = 'insert into #tmpcount (rec_count) select count(*) rec_count from mytable' --place your number concat here
exec (@sql)
--return the temp table value into a variable
set @rec_count = (select rec_count from #tmpcount)
--just printing so you can see the result
print @rec_count
--clean up the temp table
drop table #tmpcount
hth
July 29, 2008 at 8:52 am
Executing your final statement will return the value. Now if you are talking about running this in a loop you need a table variable to store the results and the do:
Insert Into @table
Exec(Sql statement)
I would also insert the table name that I am getting the rowcount from. If you really just want the row counts without a filter you could run:
[font="Courier New"]SELECT
T.name AS table_name
P.rows
FROM
sys.partitions P JOIN
sys.tables T ON
P.OBJECT_ID = T.OBJECT_ID JOIN
sys.indexes I ON
P.index_id = I.index_id AND
T.OBJECT_ID = I.OBJECT_ID
WHERE
I.index_Id <= 1 AND-- clustered index or heap
T.name IN (
)
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 29, 2008 at 8:55 am
If you must do this, parameterize the query and use sp_executesql. You can then capture output parameters. Something like this:
DECLARE @sql NVARCHAR(MAX)
DECLARE @count INT
DECLARE @tablename NVARCHAR(MAX)
SET @tablename = N'TestTable'
SET @SQL = N'SELECT @count = COUNT(*) FROM ' + @tablename
EXEC sp_executesql @sql, @params = N'@count int OUTPUT',@count = @count OUTPUT
SELECT @count
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply