Again problem with dynamic query [?]

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)

Again problem with dynamic query [?]

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-04-09 : 06:40:12

Hi guys,
I am having one more problem.
I have a query like this

select @dbname=name from #dbnames
I want to subsitute the value of @dbname in the query

select name,id from @dbname..sysobjects where xtype='u'

But I keep getting the error
Must declare the variable '@dbname'.
I want that the table name should be dynamic.

Please help me out.

Thanks.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-09 : 06:43:53

Have you tried my solution on your previous thread: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81823[/url]

What more you want? Post your complete code. It is difficult to guess just from bits and pieces.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-04-09 : 06:55:47

[quote]Originally posted by harsh_athalye

Have you tried my solution on your previous thread: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81823[/url]

Hi harsh,

Yes I tried that one .It is working perfectly.
But the problem is if i have a query like this

insert into @tblnames(id,name,tblname,dbname)select id,name,@name,'pubs' from @dbname..syscolumns where id=@id

where @dbname is supposed to dynamic. it does not seem to work.

Regards.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-09 : 07:01:54

Of course, it won't work since you will have to use D-Sql here.

declare @sql varchar(8000)
set @sql = 'insert into ' + @tblnames + '(id,name,tblname,dbname) select id,name,' + @name + ',''pubs'' from ' + @dbname + '..syscolumns where id=' + convert(varchar(10), @id)
exec(@sql)

But I strongly advise you against following such practices. Why you need this kind of query? Why table name and DB name is required to be dynamic?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-04-09 : 07:09:10

[quote]Originally posted by harsh_athalye

But I strongly advise you against following such practices. Why you need this kind of query? Why table name and DB name is required to be dynamic?

Hi harsha,
My damn boss wants to get a table structure from all the databases in a server which contains tables with same name & same coulmn name.
Anyways thanks for ur help.
I greatly appreciate it

Rgds

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources