Calling stored procedure in a database to query data in another database

15 pts.
Tags:
SQL Database
SQL Server 2005
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    I don't think you are going to find a way to do it without dynamic SQL.
    69,835 pointsBadges:
    report
  • carlosdl
    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.
    69,835 pointsBadges:
    report
  • Walrum
    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?
    270 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following