Microsoft SQL Server Create a session specific synonym

Question

I am working on a database that hold specific session data in separate tables for each session. e.g. I'd have a table called ProductsABCD, which holds products visible to that session, where ABCD is the session Id. Not graceful, but what I've got to work with. Currently I would access this data in the format:

DECLARE @Session AS VARCHAR(MAX) = 'ABCD'
DECLARE @strSQL AS VARCHAR(MAX)

SET @strSQL = 'SELECT * FROM Products' + @Session
EXEC (@strSQL)

A very simplified version, but anyone who has ever used Dynamic SQL will tell you how quickly this becomes very ugly. I am currently looking into the option of using Synonyms. I can set these up in Dynamic SQL and then Access the synonym without dynamic. e.g.

DECLARE @Session AS VARCHAR(MAX) = 'ABCD'
DECLARE @strSQL AS VARCHAR(MAX)

SET @strSQL = 'CREATE SYNONYM myProducts FOR Products' + @Session
EXEC (@strSQL)

SELECT  * 
FROM    myProducts

DROP SYNONYM myProducts

While this works when only a single user is testing, its not a viable option when multiple users are online as the synonym is globally accessible. Does anyone know of a way to create a synonym that is only accessible to a single session at a time? Similar to how a temp table can only be accessed by a single session.

Many thanks.

Additional info:

The circumstances here are that there is some business logic at play here that defines what products are visible, what prices are displayed, what stock levels are accessible etc. Each of these is stored in global tables created upon session start up, and then cleaned up either at session dispose or on a timer.

These cannot be stored in temp tables, because the lifetime expires when the setup stored proc finishes. Cannot be held in a single table with a session parameter as a field because of the high number of sessions/qty of data that is available in the system. This was developed before partitioning was really an option. Would also require major reworking of stored procedures - please remember this is an inherited existing system.

A db session here is an IIS session that opens a single connection to the database and holds that session open. This is then cleared down by a dispose session sent on log out, or via a timer that clears any old sessions that were not disposed of properly.

I would be looking for a synonym that is unique either for the period of the connection, or only during the specific call to a stored procedure. Either would work, provided the synonym is not available to another session or stored procedure.

Answer 1

Possible way to circumvent this quest is to create per-session user, put all tables in per-session schema, make this schema default to this user, run everything with execute as <session-user> and without specifying schemas before tables. Could do the thing - to keep tables with same name (but in different schemas). But you better have hothing in dbo schema in this case. And maybe try to rethink whole solution someday.

Answer 2

I know the question is a little bit old but here is another way that may help

Logic:

• Create 2 tables to save your tables friendly names (to write the query) and real name (to execute)

• Write the query with normal names in the @friendlyQuery variable

• The @realQuery is going the replace all the names and execute

Warning: Be careful with short names like "#A" because another name can replace too, or use the quotename in all the names like the example

Code:

create table #realTables (
    friendlyName varchar(100) unique
    ,realName varchar(100) )
create table #myTemps (
    friendlyName varchar(100) unique
    ,uniqueName varchar(100) default '##' + replace(newid(),'-',''))

    --Write your tables names variables

insert #realTables              values ('[productsTable]', '[productsTableABCD]')
insert #myTemps (friendlyName)  values ('[#products]')

    --Write your friendly query

declare @friendlyQuery varchar(max) = '
select top 1000
    *
into [#products]
from [productsTable]'
declare @realQuery varchar(max) = @friendlyQuery
select
    @realQuery = REPLACE(@realQuery, friendlyName, realName)
from #realTables
select
    @realQuery = REPLACE(@realQuery, friendlyName, uniqueName)
from #myTemps

print '-------> friendlyQuery' + char(13) + @friendlyQuery
print '-------> realQuery' + char(13) + @realQuery
EXEC(@realQuery) --> Magic

Plus: If you need to get the data into friendly table names, use the PRC_MATCH_INSERT like the last example