SQL Synonyms :.: Avoiding Dynamic SQL






4.63/5 (4 votes)
A first approach to avoid Dynamic SQL in stored procedures
Scenario
Multiple database scenario with the same structure, but different database name
Sometimes, we need to execute a stored procedure that manipulates table data, but don’t know which database will be, we usually pass it's name as a parameter, and fall into char
concatenation and executing like the following:
declare @db varchar(10) = 'DatabaseName' -- This is a parameter actually.
declare @sentence varchar(max)
set @sentence = 'Insert into ' + @db + _
'.dbo.Table (Field1,Field2) Values (' + @Value1 + ',' + @Value2 + ') '
EXEC (@sentence)
In complex scenarios of tables with multiple columns and datatypes, it is definitely annoying.
This is my first approach for a more elegant solution:
declare @db varchar(10) = 'DatabaseName' -- This is a parameter actually
EXEC ('CREATE SYNONYM synonym_Name FOR ' + @db + '.dbo.Table')
Insert into synonym_Name (Field1,Field2) Values (@Value1,@Value2)
DROP SYNONYM synonym_Name -- Apparently synonym can´t be updated.
Hope it helps someone.
Live long... and program!