title: SQL Server 2008: Creating dynamic Synonyms?
body: <p>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:</p>
  
  <pre><code>CREATE PROCEDURE MyTest
    @tableName nvarchar(255)
  AS
  BEGIN
    SELECT * FROM @tableName
  END
  </code></pre>
  
  <p>But we can't use parameters for table names in SQL. <a href="https://stackoverflow.com/questions/1789413/sql-statement-with-dynamic-table-names-or-redesign">So I asked you</a> and tried the solution with using Synonyms instead of a parameter for the table name:</p>
  
  <pre><code>CREATE PROCEDURE MyTest
    @tableName nvarchar(255)
  AS
  BEGIN
    EXEC SetSimilarityTableNameSynonym @tbl = @tableName;
    SELECT * FROM dbo.CurrentSimilarityTable
  END
  </code></pre>
  
  <p>SetSimilarityTableNameSynonym is a SP to set the Synonym dbo.CurrentSimilarityTable to the passed value (the specific table name). It looks like:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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()</p>
  
  <pre><code>DECLARE @theGUID uniqueidentifier;
  SET @theGUID=NEWID()
  SET @theSynonym = 'dbo.SimTabSyn_' + CONVERT(nvarchar(255), @theGUID);
  </code></pre>
  
  <p>BUT ... I can't use the dynamical created name to create a synonym:</p>
  
  <pre><code>CREATE SYNONYM @theSynonym FOR dbo.similarity_byArticle;
  </code></pre>
  
  <p>doesn't work.</p>
  
  <p>Has anybody an idea, how to get dynamical synonyms running? Is this even possible?</p>
  
  <p>Thanks in advance,
  Frank</p>