Select query of Multiple Data base, Data base name in parameter

Question

Post reply

Select query of Multiple Data base, Data base name in parameter

bvdrsganesh

SSC Enthusiast

Points: 112
More actions
May 1, 2010 at 9:00 am

#97280

Hi Great Minds,
I know we can access data of other then current data base by providing the data base name in select query.
Eg. select * from HRS.dbo.Employee H left outer join MyDB.dbo.Employee M on M.empno = H.empno
As I want to use this kind of select queries in a stored procedure of MyDB database and at the same time i cannot hard code the other data base HRS in my stored procedure.
so I expect the same result with a solution like :
////////////////////////////////////////////////////////////////////////////
declare @OthrDb varchar(20)
set @OthrDb = 'HRS'
select * from @OthrDb.dbo.Employee H left outer join dbo.Employee M on M.empno = H.empno
////////////////////////////////////////////////////////////////////////////
Please do not advice me to build this select query to a string variable and then execute it. Because I would like to use this query as normal query and also build cursors / insert / update statements.
Thanks in advance for any kind of your solutions.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

dmoldovan SSCertifiable Points: 6334 More actions · Answer 1

You can build absolutely normal queries through dynamic SQL 🙂

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 2

Dynamic SQL is the only solution that comes to my mind. Using it with sp_executesql is safe.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

prakash144 SSC Journeyman Points: 78 More actions · Answer 3

Try this

declare @db sysname

set @db='master'

exec ('use ' + @db)

Go

select * from sysobjects

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 4

Your requirement somehow sounds very weird - Passing the database name as a parameter!

If you do not want to 'hard-code' DB name, you can use synonyms.

You can read more here -

http://www.developer.com/db/article.php/3613301/Using-Synonyms-in-SQL-Server-2005.htm

http://msdn.microsoft.com/en-us/library/ms177544.aspx

- arjun

https://sqlroadie.com/

mar1jxm Ten Centuries Points: 1303 More actions · Answer 5

Try to use sp_msforeachdb

Here is link for you:

http://blogs.techrepublic.com.com/datacenter/?p=292

Good luck!

Jacob Milter

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 6

Jacob, how would he use sp_msforeachdb. He wants to execute the statement for a particular DB.

Can you explain?

-arjun

https://sqlroadie.com/