Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Goliath
Starting Member
5 Posts |
 Posted - 2006-05-02 : 04:23:25
|
| I'll illustrate my problem with an example:IF DB_NAME() IN ('ve_data', 'vk_data') SELECT * FROM vc_data.dbo.Foobar;ELSE SELECT * FROM Foobar;ENDThis is obviously duplicate code, except for the vc_data.dbo prefix in the first part. This is done because ve_data and vk_data don't have the table Foobar (or it's just an empty table).This whole thing is quind of a history-grown thing made by my predecessors... i think it's a wtf of some sort, so i'd like to know what went wrong or maybe a quick fix to get rid of the duplicate code.SET @db = 'vc_data';SELECT * FROM @db.dbo.Foobar; // This probably doesn't workWithout using dynamic sql. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-02 : 04:31:21
|
yes. You need to use Dynamic SQL to do that.Why don't you create a view to vc_data.dbo.Foobar in both ve_data and vk_data database ? Will that solve your problem ?
KH |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-05-02 : 04:37:23
|
| You can do it with the help of Dynamic SQL. But this is not advisible. Why do you want to pass db name as parameter?MadhivananFailing to plan is Planning to fail |
|
|
|
Goliath
Starting Member
5 Posts |
Posted - 2006-05-02 : 04:59:01
|
| The databases ve_data and vk_data need to look at vc_data for the contents of the Foobar table.Maybe it's possible to transparantly map ve_data.dbo.Foobar to vc_data.dbo.Foobar?Using a view would probably mean declaring it in every stored procedure? (not very flexible either) |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-02 : 05:04:07
|
"Using a view would probably mean declaring it in every stored procedure? (not very flexible either)"What do you mean by this ? You only have to create the view ONCE.
KH |
|
|
|
Goliath
Starting Member
5 Posts |
Posted - 2006-05-02 : 05:13:26
|
| Ah, indeed that would work like mapping the table to another database. Simply name the view the same as the table. The view would contain a SELECT * FROM vc_data.dbo.Foobar..Then it would use either the View or the Table depending on which one exists on that database.Will this give a lot of overhead? (doing a select* every time) |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-02 : 05:28:59
|
"Ah, indeed that would work like mapping the table to another database. Simply name the view the same as the table. The view would contain a SELECT * FROM vc_data.dbo.Foobar.."Yes. you are right."Will this give a lot of overhead? (doing a select* every time)"No. There is no difference when you when you select from view or table.
KH |
|
|
|
Goliath
Starting Member
5 Posts |
Posted - 2006-05-02 : 07:59:32
|
| Thank you very much for your help |
|
|
|
|
|
|
|
|
Dynamic database prefix