Changing database names and cross-database queries in stored procedures

Question

I have several related databases whose version is changed. Instances of different versions can work side by side, identified by their different versions, that is, [NorhwindV1.1] and [NorhwindV1.2] can be on the same server along with [SouthwindV1.1] and [SouthwindV1.2].

There are several stored procedures that perform cross-database queries, i.e. exist on NorthwindV1.1, as well as query tables in SouthwindV1.1. What is the best way to manage changes to database names by changing the version number so that stored procedures request the correct version of the databases?

Thanks, MagicAndi.

Answer 1

Assuming that the number of tables requested between the databases is manageable, you can create synonyms for these tables and use a synonym in the procedures. Then you just need to change synonyms to switch between versions.

http://msdn.microsoft.com/en-us/library/ms177544.aspx

Answer 2

There are only two ways that I know of, and both suck. The first is to use dynamic SQL, for example:

declare @db varchar(512) set @db = 'NorthwindV1.1' declare @sql varchar(max) set @sql = 'select * from ' + @db + '.dbo.YourTable' exec (@sql) 

Secondly, you need to install multiple instances if SQL Server is on the machine. Like localhost\v1.0 , localhost\v1.1 , etc. Then you can create the linked server that you request, for example:

 select * from linkedserver.northwind.dbo.YourTable 

Now you can modify the linkedserver to specify one of localhost\v1.0 , localhost\v1.1 and the subsequent stored procedures.

I will look at this question to see if the best deals appear :)

Answer 3

You can create views in each database for other required tables. The stored proc will refer to the views and the views will point to the "correct" database.

Perhaps you can even create a simple TSQL-stored proc to generate views when you need to switch them to a new database.