SQL Server Performance Forum – Threads Archive

Query with dymanic table name

Hi I am writing a stored procedure in which i want to get the count of records in the table.
the tables names are stored in another Table as TableNameMaster I have written the procedure as follows Create procedure GenerateRport
As
Begin–proc declare @tablename varchar(100), @desc varchar(500), @Count int Create table #temp
(tablename varchar(100), descr varchar(500), cnt int) delare csReport cursor for
Select Tablename, description from TableNameMaster
open csReport
Fetch next from csreport into @tablename, @desc
while @@FETCH_STATUS = 0
Begin
Print @tablename exec (‘Select @count = Count(*) from ‘ + @TableName’) insert into #temp(tablename , descr, cnt)
values (@tablename, @desc, @count) Fetch next from csreport into @tablename, @desc
end
close csReport
deallocate csReport select * from #temp end –proc this procedure doesnot give any problem with compliation, but when i execut this procedure then it shows me error Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable ‘@count’. it also shows the contents in the #temp
but the cnt colum shows all the values as null Pls can any one help me in this
Its urgent thanks in advance
Pallavi

Search this site for sp_Execute SQL and Output parameters. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Refer this
http://www.nigelrivett.net/SQLTsql/sp_executesql.html Madhivanan Failing to plan is Planning to fail

Here are the changes which will populate @count
Create procedure GenerateRport
As
Begin–proc declare @tablename varchar(100), @desc varchar(500), @Count int,@sqlcmd nvarchar(1000) Create table #temp
(tablename varchar(100), descr varchar(500), cnt int) delare csReport cursor for
Select Tablename, description from TableNameMaster
open csReport
Fetch next from csreport into @tablename, @desc
while @@FETCH_STATUS = 0
Begin
Print @tablename set @sqlcmd=’Select @count = Count(*) from ‘ + @TableName
exec sp_executesql @sqlcmd,N’@count int OUTPUT’,@count OUTPUT insert into #temp(tablename , descr, cnt)
values (@tablename, @desc, @count) Fetch next from csreport into @tablename, @desc
end
close csReport
deallocate csReport select * from #temp end –proc

Thanks Ranjit it worked, Pallavi

To avoid cursor you can make use of sysindexes table provided you run DBCC Select object_name(id),rows from sysindexes where indid<2 Madhivanan Failing to plan is Planning to fail

]]>