Calling stored procedure in a database to query data in another database
SQL 2005, SQL Query, SQL Select, SQL Server, SQL Server 2005, SQL Server 2005 administration, SQL Server stored procedures, T-SQL
Hello,
Software/Hardware used:
sql server 2005
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:
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>
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
