CODEWITHSUNDEEP
sql-serversynonym
Gratus D.
Gratus D.

Reputation: 877

SQL Server Synonym for local and remote server

I have several databases that use the same set of procedures and functions. I don't want to have to propagate all the changes to each database every time we make a change to logic of a procedure or a function.

I thought the best idea would be set up a synonym. But when I initially tried it looked like calling the synonym results in the function being called on the server where the function resides. How can I still query local data?

Below is an example:

use [DB_1]

CREATE FUNCTION ADD_FIVE(@id varchar(10))
RETURNS int
BEGIN
 DECLARE @new_value int;
 SELECT @new_value = 5+value FROM my_table WHERE ID = @id;
 RETURN @new_value;
END

GO

--now synonym
use [DB_2]

CREATE SYNONYM dbo.ADD_FIVE
FOR DB_1.dbo.ADD_FIVE

GO

My goal is that when I call 

SELECT DB_2.dbo.ADD_FIVE('someid')

EDIT: I want to get results from DB_2 (the calling context) but it seems I am only able to get result from DB_1, where the actual function is defined.

Thank you!

Upvotes: 1

Views: 2121

Answers (1)

Hannah Vernon
Hannah Vernon

Reputation: 3472

From the MSDN page for CREATE SYNONYM:

USE tempdb;
GO
-- Create a synonym for the Product table in AdventureWorks2012.
CREATE SYNONYM MyProduct
FOR AdventureWorks2012.Production.Product;
GO

-- Query the Product table by using the synonym.
USE tempdb;
GO
SELECT ProductID, Name 
FROM MyProduct
WHERE ProductID < 5;
GO

The sample above allows you to execute code from the context of tempdb that actually runs in AdventureWorks2012.

Your code is working exactly as expected. When you run SELECT DB_2.dbo.ADD_FIVE('someid') it should return results from DB_1

A synonym is typically used to extract away the name of the database or the name of the server. So instead of referencing SELECT * from MyDevServer.dbo.MyTable and having to change that to SELECT * from MyProdServer.dbo.MyTable when deploying to production, you'd setup a synonym on both servers such that the select statement becomes SELECT * from MyServer.dbo.MyTable. MyServer would be a synonym for MyDevServer in the development environment, and MyProdServer in the production environment.

I would not recommend this, but if you want to have a single place for code, perhaps it should be in the App itself. There are many benefits to having T-SQL code stored inside the database itself, not least of which is you might have several different versions for different databases, without the need for the calling code to understand the difference.

If you truly must have code callable from any database context without having the code actually reside in that database you'll need to create the code inside a stored procedure in the master database. The stored procedure name must start with sp_, and the procedure must be marked as a system procedure by using sys.sp_MS_marksystemobject.

Upvotes: 2

Related Questions