SYNONYM: An alternative to dynamic SQL


I ran into the situation where I had a stored procedure that operates on one database, but extracts information from another.

...
INSERT INTO [this].[dbo].[table_Y]
SELECT colA, colB
FROM [that].[dbo].[table_X]
...

The problem with this set up is that it is not easy to test.  Because the references to databases [this] and [that] are hard-coded, the stored procedure cannot be executed in some test environments.  It is okay if you have another server where databases [this] and [that] exists and are named [this] and [that].  But we have cases where there are many copies of each database – each used for testing different projects: [this] exists as [bob], [dick] and [harry]; [that] exists as [that_1] and [that_2].

In order to perform a test, we have to change the stored procedure.  This is not exactly an ideal solution.  It is time consuming, and introduces the possibility of errors if someone forgets to change all the stored procedures involved – or worse – publishes a test-altered stored procedure to the live environment.

I searched around for a solution to this problem.  Most forums were advocating using dynamic SQL with EXEC() or sp_executesql.  Essentially, instead of specifying the statements to be executed in the stored procedure, you build up a string containing the statements and perform an EXEC() which interprets and executes the commands.
The main problem with this approach is that the stored procedure is rather large, and building it up as a string seems to be just adding a unwanted layer of obfuscation for anyone who has to work with the code later on.

I then found this post, in which Erland Sommarskog discusses various problems with Dynamic SQL and how to deal with them.  About 3/4 of the way down the page, there is a section on getting data from another database and Erland suggests using synonyms to handle the problem.
Brilliant!

IF @sourceparm = 1 THEN
  CREATE SYNONYM local_X FOR [tom].[dbo].[table_X];
ELSE IF @sourceparm = 2 THEN
  CREATE SYNONYM local_X FOR [dick].[dbo].[table_X];
ELSE IF @sourceparm = 3 THEN
  CREATE SYNONYM local_X FOR [harry].[dbo].[table_X];
ELSE
  CREATE SYNONYM local_X FOR [this].[dbo].[table_X];
...
IF @destinatiomparm = 1 THEN
  CREATE SYNONYM remote_Y FOR [that_1].[dbo].[table_Y];
ELSE IF @destinatiomparm = 2 THEN
  CREATE SYNONYM remote_Y FOR [that_2].[dbo].[table_Y];
ELSE
  CREATE SYNONYM remote_Y FOR [that].[dbo].[table_Y];
...
INSERT INTO remote_Y
SELECT colA, colB
FROM local_X
...
DROP SYNONYM local_X;
DROP SYNONYM remote_Y;
...

With this method, we can link any two databases simply by specifying some input parameters.  We do not have to tamper with the stored procedure just to run it in a test environment.  Actually, in our case it is even easier, as the server name is all we need to know in order to decide which two databases should be used.  The IF statement simply examines the @@SERVERNAME variable to determine what synonyms to use.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s