Home > IT Answers > SQL Server > Calling stored procedure in a database to que...
Ssss123
15 pts.
 Calling stored procedure in a database to query data in another database
SQL Server 2005, SQL Database
Hello, I am writing a stored procedure that will query data from another database, but the database name is variable. Do I have to use a dynamic query? We dont want to use dynamic queries because of performance loss. But is there a way to do it without a dynamic query? Example GetData 'some_remote_db_name'; and the select statment would be like SELECT data FROM data_base_name.table; --or something like that Any help is greatly appriciated Thanks Software/Hardware used: sql server 2005
ASKED: Feb 14, 2012  10:25 AM GMT
UPDATED: February 27, 2012  8:43:41 PM GMT
RELATED QUESTIONS
RichardSun
30 pts.
Answer Wiki:
If I understand you right, I guess the following is something that you may want to try. Hope it helps.

create procedure p1
as
select ... from db1.tb
go

create procedure p2
as
select ... from db2.tb
go

...

create procedure p
@db varchar(20)
as
if @db='db1'
exec p1;
else if @db='db2'
exec p2;
else
exec ...
go
Last Wiki Answer Submitted:  Feb 14, 2012  8:13 PM (GMT)  by  RichardSun   30 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

I don’t think you are going to find a way to do it without dynamic SQL.

Carlosdl
 60,245 pts.
 

The answer suggested in the Wiki would work if you have a pre-defined set of possible database names.

If the database names are truly “variable” and not known beforehand, that solution wouldn’t work.

Carlosdl
 60,245 pts.
 

What is the purpose of using database in variable? Are you dealing with only one database that is going to be query or you expect that variable could hold the name one of many databases. Is the name of database or databases known before you query?

Walrum
 270 pts.