Replacing region specific blocks of code, T-SQL Synonyms

When dealing with different database names per country/region it is quite common to write the following code

IF @Region = @Const_Ireland
BEGIN
Select * from DATABASENAME_IE.dbo.Orders where........
Update DATABASENAME_IE.dbo.Orders Set OrderDate=... where........
etc etc
END
IF @Region = @Const_UK
BEGIN
Select * from DATABASENAME_UK.dbo.Orders where........
Update DATABASENAME_UK.dbo.Orders Set OrderDate=.... where........
etc etc
END

This creates huge blocks of repeated code where the only thing that changes is the database name. Indeed you often see completely different stored procedures in some cases to simply reference different database names.

One of the many problems with this is code maintenance. If you have to make any kind of change to fix something or add a new field to the table names you have to ensure you make it in all the correct places etc.

Also if we want to add a new region (and as a result a new database) we have to duplicate a LOT of code simply to reference this new database name.

Ideally to get around this you would like to use variable table names such as

declare @dbname nvarchar(50)
Set @dbname = 'DatabaseName_UK'
select * from @dbname.dbo.Orders

Because SQL Server needs to verify security etc it will not allow this type of variable database name syntax. The nearest that can be found to get around this problem is a new SQL sever 2005 feature called Synonyms.

This allows you to do the following at the top of the Stored Procedure

IF @Region = @Const_Ireland
Create Synonym RegionFreeOrders for DatabaseName_IE.dbo.Orders
If @RegionID =@Const_UK
Create Synonym RegionFreeOrders for DatabaseName_UK.dbo.Orders
If @RegionID =@Const_USA
Create Synonym RegionFreeOrders for DatabaseName_US.dbo.Orders

Then reference the Virtual synonym table name RegionFreeOrders throughout the SP regardless of the region and database name you are dealing with

Select * from RegionFreeOrders where........
Update RegionFreeOrders Set OrderDate=...... where........
etc etc
Drop Symonym RegionFreeOrders

So if a new region is added the Stored Procedures will still have to be changed, but only to add an additional if statement at the top of each procedure with no duplicated logic code in the SP....

More information here: Synonym Transact-SQL Enhancements