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' declare @sentence varchar(max)
set @sentence = 'Insert into ' + @db + _
'.dbo.Table (Field1,Field2) Values (' + @Value1 + ',' + @Value2 + ') '
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'
EXEC ('CREATE SYNONYM synonym_Name FOR ' + @db + '.dbo.Table')
Insert into synonym_Name (Field1,Field2) Values (@Value1,@Value2)
DROP SYNONYM synonym_Name
Hope it helps someone.
Live long... and program!