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

5 pts.
Tags:
SQL 2005
SQL Query
SQL Select
SQL Server
SQL Server 2005
SQL Server 2005 administration
SQL Server stored procedures
T-SQL
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: July 19, 2010  3:20 PM
UPDATED: February 14, 2012  10:24 AM

Answer Wiki

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

When you say the name is “variable”, do you mean it can be a new value on each call, or do you mean it could be one of a set of predefined database names?

If the name is truly variable and unknown at compile time, then you will need to use dynamic SQL to build and execute your query.

If the name(s) are known at compile time, but you just want to be able to select from one of a set of databases, then you can use a series of IF or CASE statements at run time, e.g.:

<pre>procedure GetData (
vFromDatabase varchar(255)
)

if vFromDatabase = ‘DBOne’ then
select Data from DBOne.TableX where …
if vFromDatabase = ‘DBTwo’ then
select Data from DBTwo.TableX where …</pre>

Discuss This Question:  

 
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

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