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.
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.