Click here to Skip to main content
12,447,625 members (59,214 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

8.6K views
7 bookmarked
Posted

SQL Synonyms :.: Avoiding Dynamic SQL

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

Pro
Pro

Comments and Discussions

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