SQL Server 2008: Creating dynamic Synonyms?

Question

Hi folks,

in my SQL Server 2008 database I have a number of different tables with the same structure. I query them in different stored procedures. My first try was to pass the table name to the stored procedure, like:

CREATE PROCEDURE MyTest
  @tableName nvarchar(255)
AS
BEGIN
  SELECT * FROM @tableName
END

But we can't use parameters for table names in SQL. So I asked you and tried the solution with using Synonyms instead of a parameter for the table name:

CREATE PROCEDURE MyTest
  @tableName nvarchar(255)
AS
BEGIN
  EXEC SetSimilarityTableNameSynonym @tbl = @tableName;
  SELECT * FROM dbo.CurrentSimilarityTable
END

SetSimilarityTableNameSynonym is a SP to set the Synonym dbo.CurrentSimilarityTable to the passed value (the specific table name). It looks like:

CREATE PROCEDURE [dbo].[SetSimilarityTableNameSynonym] 
@tbl nvarchar(255)
AS
BEGIN
  IF object_id('dbo.CurrentSimilarityTable', 'SN') IS NOT NULL 
    DROP SYNONYM CurrentSimilarityTable;

  -- Set the synonym for each existing table
  IF @tbl = 'byArticle'
    CREATE SYNONYM dbo.CurrentSimilarityTable FOR dbo.similarity_byArticle;

  ...
END

Now, as you probably see, the problem is with concurrent access to the SPs which will "destroy" each others assigned synonym. So I tried to create dynamic synonyms for each single SP-call with a GUID via NewID()

DECLARE @theGUID uniqueidentifier;
SET @theGUID=NEWID()
SET @theSynonym = 'dbo.SimTabSyn_' + CONVERT(nvarchar(255), @theGUID);

BUT ... I can't use the dynamical created name to create a synonym:

CREATE SYNONYM @theSynonym FOR dbo.similarity_byArticle;

doesn't work.

Has anybody an idea, how to get dynamical synonyms running? Is this even possible?

Thanks in advance, Frank

Answer 1

All I can suggest is to run the CREATE SYNONYM in dynamic SQL. And this also means your code is running at quite high rights (db_owner or ddl_admin). You may need EXECUTE AS OWNER to allow it when you secure the code.

And how many synonyms will you end up with for the same table? If you have to do it this way, I'd use OBJECT_ID not NEWID and test first so you have one synonym per table.

But if you have one synonym per table then why not use the table name...?

What is the point is there creating 1 or more synonyms for the same table, given the table names are already unique...

I'd fix the database design.

Answer 2

Why would you want multiple concurrent users to overwrite the single resource (synonym)? If your MyTest procedure is taking a the table name as a parameter, why not simply do dynamic SQL? You can validate the @tableName against against a hardcoded list of tables that this procedure is allowed to select from, or against sys.tables