Dynamic Query - 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

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-04-09 : 06:09:00

Hi guys,
I have a statement like this

declare @strsql varchar(1000)
declare @dbname varchar(200)
set @strsql='select name,id from'+ @dbname +'..sysobjects where xtype=u'
The value of @dbname is dynamic.It changes according to the number of databases in the server.
But when i run exec @strsql it says

Incorrect syntax near the keyword 'exec'.

What must be I missing here?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-09 : 06:10:18

You are missing parenthesis!

declare @strsql varchar(1000)
declare @dbname varchar(200)
set @dbname = 'master'
set @strsql='select name,id from '+ @dbname +'..sysobjects where xtype=''u'''
exec(@strsql)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-04-09 : 06:14:49

Hi harsh
Than u very much for ur reply.I greatly appreciate it.
Thanks once again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-10 : 05:22:34

www.sommarskog.se/dynamic_sql.html for more informations on Dynamic sql

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources