CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
user194076
user194076

Reputation: 9017

Dynamic database instance in a query

Okay. I have a database that has bunch of stored procedures and references another database like this: 

select * from DatabaseName.dbo.tableName... or
Insert into DatabaseName.dbo.tableName....

So. It references it around 500-1000 times in stored procedures. Now, what happens is that I need to run these stored procedures from one database referencing another database, like this:

 select * from AnotherDatabaseName.dbo.tableName... or
 Insert into AnotherDatabaseName.dbo.tableName....

As far as I understand sql server 2008 r2 does not have anything new that could help it and I have to build dynamic queries every time with a parameter, right? If this is the case, is there any decent easy way to do it? I need to change every stored procedure and add a parameter @DatabaseInstance. Also, I need to reconfigure all of the queries. sounds scary. Any automated tool or something to help with that?

Upvotes: 0

Views: 142

Answers (1)

Michal Barcik
Michal Barcik

Reputation: 662

Using code you can create synonym for the database in your DB. I.e. I'm using this code in SP to insert into specified database based on parameter (region)

IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = N'TABLE_SYNONYM') DROP SYNONYM [dbo].[TABLE_SYNONYM]
IF @region_id not in (2,3) CREATE SYNONYM [dbo].[TABLE_SYNONYM] FOR [DB_US].[dbo].[mytable]
IF @region_id = 2 CREATE SYNONYM [dbo].[TABLE_SYNONYM] FOR [DB_EU].[dbo].[mytable]
IF @region_id = 3 CREATE SYNONYM [dbo].[TABLE_SYNONYM] FOR [DB_Asia].[dbo].[mytable]

insert into [dbo].[TABLE_SYNONYM] (...) select ....

Upvotes: 1

Related Questions