| Author |
Topic |
|
safderalimd
Starting Member
32 Posts |
 Posted - 2007-05-02 : 15:54:12
|
| Is there any way around in sql 2005 to not to hard code <db.user.table> in select statements with in stored procedures. I am having lots of problem when moving stored procedures from one server to other as the dtabase name is not same. I have to manully edit stored procedures to change the table names qualified by database name in all select statements. How do you guys manage when we have 1000 of stored procedures? I do not want to store the select SQL in string and then execute it. Its not good for performance. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-02 : 16:12:06
|
| You shouldn't be using the three part naming convention in your stored procedures if the database name will ever change. Just use the two part naming convention (ObjectOwner.ObjectName) and then handle which database it is in via your connection string in your application.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-05-02 : 16:38:00
|
| What Tara said.. However, if you are referencing a table in another database, I'd suggest creating a view to that other database and use the three-part naming convention there and the standard two-part naming convention in your stored procedures to access the view. |
|
|
|
safderalimd
Starting Member
32 Posts |
Posted - 2007-05-03 : 13:05:58
|
| So you are moving the hard coded part from stored procedures to views and I still need to manually change 1000s of views when ever I move accross different testing/prod environments. Right? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-03 : 13:46:22
|
| Show us a simple example of what you are doing so that we can tell you if you can just do it in the connection string or need an alternative solution.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-05-03 : 14:03:12
|
quote:
Originally posted by Lamprey
What Tara said.. However, if you are referencing a table in another database, I'd suggest creating a view to that other database and use the three-part naming convention there and the standard two-part naming convention in your stored procedures to access the view.
Can you elaborate on that? I guess that would be better, as you would only have to maintain the views on a change, instead off all the sprocs...Why didn't you say that earlier...I just finished fixing some guys mess...and that would have been useful...but honestly, I don't seeeing db..table to referencing another database actually changing environment to environmentAnyone else do this? What about performance?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-05-03 : 15:29:36
|
quote:
Originally posted by X002548
Can you elaborate on that?Why didn't you say that earlier...What about performance?
Basically, it you have to access tables from other databases I always suggest that the access point be controlled. And I think the easiest way to do that is with a View. I'm not sure if there is something I can elaborate on that can help you, but if there is a particular point, please ask and I’ll see if I can answer it.You never asked! :)Performance seems just as good at doing three part naming in a stored procedure. I know there are some different opinions about using NOLOCK. But, I tend to use NOLOCK for views that reference tables outside the current database, unless they need to be able to update those tables. And occasionally, I've created read-only and writable views just to try to prevent unnecessary locking.-Ryan |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-05-03 : 15:32:32
|
I don't really know much about them, but 2005 has the concept of a SYNONYM (CREATE SYNONYM). This may or maynot help, but I thought I'd throw it out there in case it does indeed work and makes sense to use. Feel free to check it out in BOL. For example:CREATE SYNONYM SalesHistoryFOR SalesData.dbo.SalesHistory;GO -Ryan |
|
|
|
|
hard coded <db.user.table> in stored procedure