65.9K
CodeProject is changing. Read more.
Home

SQL Synonyms :.: Avoiding Dynamic SQL

starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.63/5 (4 votes)

Oct 24, 2013

CPOL
viewsIcon

16791

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!