Alternate synonym in SQL Server in one transaction

Question

I am new to Transact SQL programming.

I have created a stored procedure that would drop and create an existing synonym so that it will point to another table. The stored procedure takes in 2 parameters:

synonymName - an existing synonym
nextTable - the table to be point at

This is the code snippet:

...
BEGIN TRAN SwitchTran
   SET @SqlCommand='drop synonym ' + @synonymName
   EXEC sp_executesql @SqlCommand
   SET @SqlCommand='create synonym ' + @synonymName + ' for ' + @nextTable
   EXEC sp_executesql @SqlCommand
COMMIT SwitchTran
...

We have an application that would write data using the synonym regularly.

My question is would I run into a race condition where the synonym is dropped, while the application try to write to the synonym?

If the above is a problem, could someone give me suggestion to the solution.

Thanks

Those are called store**D** procedures - as store**D** inside SQL Server - not "store* procedures.... — marc_s, Nov 12 '10 at 16:52

score 2 · Accepted Answer · answered Nov 12 '10 at 18:16

Yes, you'd have a race condition.

One way to manage this is to have sp_getapplock after BEGIN TRAN in Transaction mode and trap/handle the return status as required. This will literally serialise (in the execution sense, not isolation) callers so only one SPID executes at any one time.

score 1 · Answer 2 · edited May 23 '17 at 12:19

I'm fairly certain you will indeed get race conditions. Synonym Names are intended to be used for shortening the name of an object and aren't supposed to change any more often than other objects. I'm guessing by your description that you are using it for code reuse. You are probably better off using Dynamic SQL instead, which incidentally you already are.

For more information on Dynamic SQL you might want to consider a look at this article on by Erland Sommarskog that OMG Poinies references in a lot of his answers. Particularly the section on Dealing with Dynamic Table and Column Names which I've quotes below

Dealing with Dynamic Table and Column Names

Passing table and column names as parameters to a procedure with dynamic SQL is rarely a good idea for application code. (It can make perfectly sense for admin tasks). As I've said, you cannot pass a table or a column name as a parameter to sp_executesql, but you must interpolate it into the SQL string. Still you should protect it against SQL injection, as a matter of routine. It could be that bad it comes from user input.

To this end, you should use the built-in function quotename() (added in SQL 7). quotename() takes two parameters: the first is a string, and the second is a pair of delimiters to wrap the string in. The default for the second parameter is []. Thus, quotename('Orders') returns [Orders]. quotename() takes care of nested delimiters, so if you have a really crazy table name like Left]Bracket, quotename() will return [Left]]Bracket].

Note that when you work with names with several components, each component should be quoted separately. quotename('dbo.Orders') returns [dbo.Orders], but that is a table in an unknown schema of which the first four characters are d, b, o and a dot. As long as you only work with the dbo schema, best practice is to add dbo in the dynamic SQL and only pass the table name. If you work with different schemas, pass the schema as a separate parameter. (Although you could use the built-in function parsename() to split up a @tblname parameter in parts.)

While general_select still is a poor idea as a stored procedure, here is nevertheless a version that summarises some good coding virtues for dynamic SQL:

CREATE PROCEDURE general_select @tblname nvarchar(128),
                                @key     varchar(10),
                                @debug   bit = 0 AS DECLARE @sql nvarchar(4000) 
            SET @sql = 'SELECT col1, col2, col3
            FROM dbo.' + quotename(@tblname) + '
            WHERE keycol = @key' IF @debug = 1
            PRINT @sql EXEC sp_executesql @sql, N'@key varchar(10)', @key = @key 

            - I'm using sp_executesql rather than EXEC().
            - I'm prefixing the table name with dbo. 
            - I'm wrapping @tblname in quotename(). 
            - There is a @debug parameter.

Alternate synonym in SQL Server in one transaction

2 Answers2

Linked