Passing a database name as a parameter

Does SQL Server allow you to pass in a Database name and use it in a stored
procedure? I need to do a join on two tables in two different databases.
Here is some code example that I'm trying to accomplish
//call in C# to add a parameter to the command object
cmd.AddInParameter("@.dbname", DbType.String, "dbName.dbo");
--Stored Procedure--
DECLARE @.dbname varchar(50) (this will be "dbname.dbo")
SELECT * From
table1 INNER JOIN @.dbname ON table1.column1ID = @.dbname.column1ID
Sooooooooo....this is quite a scaled down version of the stored procedure
I'm working on, but the premise is that I have to join tables in two
different databases and I want to be able to do this without having to hard
code "dbname.dbo." for every plase in the SP that is accessing tables from
this second database because eventually the SP will be broken when the
database is moved from our development environment. All references to that
one database would have to be different for each environment we have.
Thanks in advance,
John Scott."John Scott" <johnscott@.despammed.com> wrote in message
news:327E4E45-53BE-44C9-815C-865E2E4E384C@.microsoft.com...
> Does SQL Server allow you to pass in a Database name and use it in a
> stored
> procedure? I need to do a join on two tables in two different databases.
>
> Here is some code example that I'm trying to accomplish
> //call in C# to add a parameter to the command object
> cmd.AddInParameter("@.dbname", DbType.String, "dbName.dbo");
>
> --Stored Procedure--
> DECLARE @.dbname varchar(50) (this will be "dbname.dbo")
> SELECT * From
> table1 INNER JOIN @.dbname ON table1.column1ID = @.dbname.column1ID
>
>
> Sooooooooo....this is quite a scaled down version of the stored procedure
> I'm working on, but the premise is that I have to join tables in two
> different databases and I want to be able to do this without having to
> hard
> code "dbname.dbo." for every plase in the SP that is accessing tables from
> this second database because eventually the SP will be broken when the
> database is moved from our development environment. All references to
> that
> one database would have to be different for each environment we have.
> --
> Thanks in advance,
> John Scott.
I would either create a view to reference the table in the other database or
I would create a synonym (in 2005 only). That way the database name is only
coded in one place per object and it's easy to parameterize that db name at
install time. Much easier than trying to do it dynamically in a proc anyway.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the very quick reply David!!
Unfortuanately we are using SQL 2000 and as far as creating a view to
represent a certain table from the second database the database reference in
each view would have to be changed to point at the proper database when
switching environments. I basically need a configureable way to change the
reference to this second database..
if i can't pass in a parameter value....I could live with something like
this:
----
--
DECLARE @.dbname varchar(50)
set @.dbname = "dbname.dbo"
SELECT * From
table1 INNER JOIN @.dbname ON table1.column1ID = @.dbname.column1ID
----
--
Can I do that? Or is there a way to represent a variable as a database
object somehow'
Thanks again,
John Scott.
"David Portas" wrote:

> "John Scott" <johnscott@.despammed.com> wrote in message
> news:327E4E45-53BE-44C9-815C-865E2E4E384C@.microsoft.com...
> I would either create a view to reference the table in the other database
or
> I would create a synonym (in 2005 only). That way the database name is onl
y
> coded in one place per object and it's easy to parameterize that db name a
t
> install time. Much easier than trying to do it dynamically in a proc anywa
y.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||John Scott wrote:
> each view would have to be changed to point at the proper database when
> switching environments.
Exactly. Do that in the installation script when you create the views.
If you mean you must switch environments at runtime then I'd say that
was best achieved through connection strings in client code, but that
depends what you are doing of course.

> if i can't pass in a parameter value....I could live with something like
> this:
> ----
--
> DECLARE @.dbname varchar(50)
> set @.dbname = "dbname.dbo"
> SELECT * From
> table1 INNER JOIN @.dbname ON table1.column1ID = @.dbname.column1ID
> ----
--
>
> Can I do that? Or is there a way to represent a variable as a database
> object somehow'
>
Dynamic SQL. But as general solution that's a nightmare way to kill a
database. Make sure you read:
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--