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: 102
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

dmoldovan

SSCertifiable

Points: 6334

May 3, 2010 at 4:39 am

#1161175

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]