Click here to Skip to main content
13,002,355 members (76,099 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

9.3K views
7 bookmarked
Posted 24 Oct 2013

SQL Synonyms :.: Avoiding Dynamic SQL

, 29 Oct 2013
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

You may also be interested in...

Comments and Discussions

 
SuggestionNot thread safe Pin
granadaCoder15-Mar-17 5:50
membergranadaCoder15-Mar-17 5:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170626.1 | Last Updated 29 Oct 2013
Article Copyright 2013 by Claudio cespon
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid