Click here to Skip to main content
11,502,348 members (63,595 online)
Click here to Skip to main content

SQL Synonyms :.: Avoiding Dynamic SQL

, 29 Oct 2013 CPOL 7.1K 7
Rate this:
Please Sign up or sign in to vote.
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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Claudio cespon
Web Developer
Argentina Argentina
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150520.1 | Last Updated 29 Oct 2013
Article Copyright 2013 by Claudio cespon
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid