Building dynamic tsql statements in a loop

[Steve]

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 ' + @retrievedTable Name
)

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

[Trevor Best]

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 ' + @retrievedTable Name
)

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

[Simon Hayes]

st**********@ya hoo.com (Steve) wrote in message news:<6f******* *************** ****@posting.go ogle.com>...

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 ' + @retrievedTable Name
)

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

Here is one way:

declare @rec_count int,
@retrievedTable Name sysname,
@sql nvarchar(4000)
set @retrievedTable Name = 'sysobjects'
set @sql = N'select @rec_count = count(*) from ' + @retrievedTable Name
exec sp_executesql @sql, N'@rec_count int output', @rec_count OUTPUT
select @rec_count

Simon

[Steve]

sq*@hayes.ch (Simon Hayes) wrote in message news:<60******* *************** ****@posting.go ogle.com>...

st**********@ya hoo.com (Steve) wrote in message news:<6f******* *************** ****@posting.go ogle.com>...

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 ' + @retrievedTable Name
)

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

Here is one way:

declare @rec_count int,
@retrievedTable Name sysname,
@sql nvarchar(4000)
set @retrievedTable Name = 'sysobjects'
set @sql = N'select @rec_count = count(*) from ' + @retrievedTable Name
exec sp_executesql @sql, N'@rec_count int output', @rec_count OUTPUT
select @rec_count

Simon

Thanks Simon, you just saved me from a lot of tedious work.

Steve