using tables from multiple databases

[aajay]

I have store procs in my database
I need to use table from different databases
all want to do is declare a variable and pass the database name to get the data
e.g
declare @dbname
set @dbname = Fortunedb
select name, id from
@dbname +'.'+ vwGiving_Gift_Journal_RPT

so I can change the @dbname to look data at other database which ofcourse have same schema.
any idea.
thanks,

 

[LNBruno]

Look up "dynamic SQL" in Books on Line.

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[bborissov]

You must use so called dynamic SQL:

declare @dbname
set @dbname  = 'Fortunedb'

DECLARE @lcSQL varchar(2000)
SET @lcSQL = 'select name, id from '+ @dbname +'.dbo.vwGiving_Gift_Journal_RPT'
EXEC (@lcSQL)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLSister]

And of course you are aware that dynamic SQL is bad right? YOU have to set rights at the table level and it is open to SQl injection attacks and very hard to debug all possible values that will be sent of your variables. Many companies do not allow the use of dynamic SQl ontheir servers.

If you don't have too many databases, it would be better to write if statements and separate SQL for each database. Yes the code is longer but it is more secure and easier to debug properly.

if @dbname = 'fortunedb'
begin
select name, id from fortunedb.dbo.vwGiving_Gift_Journal_RPT
end




"NOTHING is more important in a database than integrity." ESquared

 

[aajay]

I can not use dynamic SQL for security reason