Dynamic query error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic query error

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-08 : 08:40:10

Mahesh writes "Hi,

I have two test tables: Test1 and Test2.
I want to know the count of one of the tables dynamically. The table number will be passed to me at runtime.

I have this simple code:

declare @table_number int
declare @count int
set @table_number = 1

exec('select @count = count(*) from Test'+convert(varchar, @table_number))

print @count

Can you please tell me what's wrong with the above code?
When I execute it, I get the follwing error:
syntax error at convert

Any help is greatly appreciated"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 08:55:52

if you need to pass in / out parameters, you will need to use sp_executesql


declare @table_number int
declare @count int
declare @sql   nvarchar(4000)
set @table_number = 1
-- you can't use convert() by itself
select @sql = 'select @count = count(*) from Test'+convert(varchar(10), @table_number)
exec sp_executesql @sql, N'@count int output', @count output

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-08 : 08:56:42

Refer this
http://www.nigelrivett.net/SQLTsql/sp_executesql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources