Steve wrote:
Hi;
I would like to read a list of tables from a temp table and then do a
sql statement on each table name retrieved in a loop, ie:
-- snip cursor loop where cursor contains a list of tables
declare @rec_count int
set @rec_count = 0
exec('select @rec_count = count(myfield) from ' + @retrievedTableName
)
This does not work. SQLSERVER tells me @rec_count is not declared.
How can I get the @rec_count populated....or can I?
Thanks in advance
Steve
From memory, use the sp_executesql (sp_execsql?) system stored
procedure, that will enable you to have an output parameter.
For this particular instance you might (again if memory serves) simply use:
set @rec_count = exec('select count(*) from ' + @...)
although you may want to build your sql string into a delcared varchar
variable first and stick that into the brackets as I've had problems
using dynamic sql this way and it will aid debugging as your string
concatenation and dynamic sql will be on separate lines of code.
--
Error reading sig - A)bort R)etry I)nfluence with large hammer