Configuration fIle

I'm using stored procedures in a database that use data in other databases. I refer these data as DataBaseName.dbo.TableName. It works fine. But, i just thought, what if the name of the databases change?

Is there anyway to use some sort of variables to refer the databases? like a configuration file used in programming applications.

Thanks

You can do one of the below:

1. In SQL Server 2005, you can create synonyms for the tables in the other database. And when the name of the database changes you will have to drop & recreate the synonym

2. In older versions of SQL Server, you can create views that point to the tables in the other database and do the same.

Concatenating a fully qualified name

In the code below, the DatabaseName and table_id are variables. The table_id
is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
that I can pass it to the parameter @.database. In other words, how do I
concatenate so that it will be something like this @.db.dbo.tblName:
CREATE PROCEDURE sampleProcedure
(
@.database varchar (100),
@.table_id uniqueidentifier
)
AS
SET NOCOUNT ON
DECLARE @.db varchar (100)
SELECT @.db = @.database
INSERT INTO DatabaseName.dbo.tblName
SELECT * FROM tblName where table_id=@.table_idHello,
You need to use Dynamic SQL to do this.After that execute the Dynamic SQL
using EXEC or SP_ExecuteSQL. Take a look int the article.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92783839-67A2-4880-938D-699519E3A9C0@.microsoft.com...
> In the code below, the DatabaseName and table_id are variables. The
> table_id
> is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
> that I can pass it to the parameter @.database. In other words, how do I
> concatenate so that it will be something like this @.db.dbo.tblName:
> CREATE PROCEDURE sampleProcedure
> (
> @.database varchar (100),
> @.table_id uniqueidentifier
> )
> AS
> SET NOCOUNT ON
> DECLARE @.db varchar (100)
> SELECT @.db = @.database
> INSERT INTO DatabaseName.dbo.tblName
> SELECT * FROM tblName where table_id=@.table_id

Concatenating a fully qualified name

In the code below, the DatabaseName and table_id are variables. The table_id
is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
that I can pass it to the parameter @.database. In other words, how do I
concatenate so that it will be something like this @.db.dbo.tblName:
CREATE PROCEDURE sampleProcedure
(
@.database varchar (100),
@.table_id uniqueidentifier
)
AS
SET NOCOUNT ON
DECLARE @.db varchar (100)
SELECT @.db = @.database
INSERT INTO DatabaseName.dbo.tblName
SELECT * FROM tblName where table_id=@.table_idHello,
You need to use Dynamic SQL to do this.After that execute the Dynamic SQL
using EXEC or SP_ExecuteSQL. Take a look int the article.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92783839-67A2-4880-938D-699519E3A9C0@.microsoft.com...
> In the code below, the DatabaseName and table_id are variables. The
> table_id
> is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
> that I can pass it to the parameter @.database. In other words, how do I
> concatenate so that it will be something like this @.db.dbo.tblName:
> CREATE PROCEDURE sampleProcedure
> (
> @.database varchar (100),
> @.table_id uniqueidentifier
> )
> AS
> SET NOCOUNT ON
> DECLARE @.db varchar (100)
> SELECT @.db = @.database
> INSERT INTO DatabaseName.dbo.tblName
> SELECT * FROM tblName where table_id=@.table_id

Concatenating a fully qualified name

In the code below, the DatabaseName and table_id are variables. The table_id
is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
that I can pass it to the parameter @.database. In other words, how do I
concatenate so that it will be something like this @.db.dbo.tblName:
CREATE PROCEDURE sampleProcedure
(
@.database varchar (100),
@.table_id uniqueidentifier
)
AS
SET NOCOUNT ON
DECLARE @.db varchar (100)
SELECT @.db = @.database
INSERT INTO DatabaseName.dbo.tblName
SELECT * FROM tblName where table_id=@.table_id
Hello,
You need to use Dynamic SQL to do this.After that execute the Dynamic SQL
using EXEC or SP_ExecuteSQL. Take a look int the article.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92783839-67A2-4880-938D-699519E3A9C0@.microsoft.com...
> In the code below, the DatabaseName and table_id are variables. The
> table_id
> is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
> that I can pass it to the parameter @.database. In other words, how do I
> concatenate so that it will be something like this @.db.dbo.tblName:
> CREATE PROCEDURE sampleProcedure
> (
> @.database varchar (100),
> @.table_id uniqueidentifier
> )
> AS
> SET NOCOUNT ON
> DECLARE @.db varchar (100)
> SELECT @.db = @.database
> INSERT INTO DatabaseName.dbo.tblName
> SELECT * FROM tblName where table_id=@.table_id