Changing Database Names and Cross Database Queries In Stored Procedures

Question

Hi,

I have a number of related databases that are version-ed. Instance of different versions may run side by side, identified by their different versions, i.e. [NorhwindV1.1] and [NorhwindV1.2] may be on the same server, along with [SouthwindV1.1] and [SouthwindV1.2].

There are a number of stored procedures that make cross database queries, i.e. exist on NorthwindV1.1 and also query tables in SouthwindV1.1. What is the best way to manage the change in database names with changing version number, so that the stored procedures query the correct version of the databases?

Thanks, MagicAndi.

Answer 1

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

declare @db varchar(512)
set @db = 'NorthwindV1.1'

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

The second is to install multiple instances if SQL Server on a machine. Like localhost\v1.0, localhost\v1.1, and so on. You can then create a linked server which you query like:

select * from linkedserver.northwind.dbo.YourTable

Now you can change the linkedserver to point at one of localhost\v1.0, localhost\v1.1 and the stored procedures will follow.

I'll be watching this question to see if better suggestions pop up :)

Answer 2

Assuming the number of tables queried between the databases is manageable, you could create synonyms for those tables and use the synonym in the procedures. You would then just need to change the synonyms to switch between versions.

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

Answer 3

You could create views within each database for the other tables needed. The stored proc's would reference the views and the views would point to the "correct" database.

You could probably even create a simple TSQL stored proc to generate the views when they need to be switched to a new database.