January 30, 2008 at 8:21 am
goran -
That really wasn't called for. Considering we're discussing "best practices" and a rule of thumb that's been bandied about a LOT, talking about what if's is in fact VERY relevant. Also - I don't recall Gila mentioning that particular scenario, and I CAN think of several perfectly plausible scenarios where you might have the same procedure names in different databases. It may be "kindergarten" per your description, but where I work - kindergarten has a tendency to happen a LOT unless you keep cracking down on standards.
That being said - I just ran a test - and it looks to me that the exact opposite is happening. The user instance overrides the instance in master when called from the user instance, which is disturbing on a whole different layer (like - you could override "sp_who" for example in a user instance....) Both 2000 and 2005 seem to do the same?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 30, 2008 at 8:34 am
Secondary comment - which gets kind of interesting as to the scoping issue.
If the procedure in MASTER is marked as a system stored proc, then it overrides the user DB's version even when the execution scope is the user DB. If it is NOT - then the opposite happens. So - you can't "override" or shadow sp_who from a user database.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 30, 2008 at 11:36 am
goran (1/30/2008)
WTF, this is like kindergarten!Gail, when you have found one (1!) application in the whole universe that uses the same name on a system stored procedure as on a user stored procedure, then you can call me. Until then don't waste my time with extremely special scenarios that u need to be on crack to fully appreciate.
So glad to be of service. If it's a waste of your time, you're most welcome not to bother reading it.
One point that you may have missed - future changes. Can you guarantee that is you name a proc sp_getAdminUsers (which is not currently a system proc) that MS won't introduce one with exactly the same name in a future version of SQL?
Why take the risk (and the minor performance hit) if it's easy to avoid totally?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2008 at 11:48 am
Matt Miller (1/30/2008)
Secondary comment - which gets kind of interesting as to the scoping issue.If the procedure in MASTER is marked as a system stored proc, then it overrides the user DB's version even when the execution scope is the user DB. If it is NOT - then the opposite happens. So - you can't "override" or shadow sp_who from a user database.
One more interesting thing on the scoping. When I tested this on SQL 2000 (haven't tried on 2005), I found that there's one way to create sp_who in a user database and, when you call it, to get your user proc not the system proc in master.
Create the proc as SomeUser.sp_who (instead of dbo) and then call it as Exec SomeUser.sp_who. Don't know if you get the same behaviour in SQL 2005 with schemas or not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2008 at 11:18 pm
One thought to share here, I usually advocate naming stored procedures, functions, etc. by object then action as follows:
usp_Customer_Insert
usp_Customer_Delete
usp_Customer_Update
usp_Customer_Select
usp_Customer_OrderList
fn_Customer_OrderList
etc.
The prefix (other than sp_) is pretty much up for grabs but I find that when I'm working it helps to have stored procedures and functions that affect a particular object grouped together as most tools including Enterprise Manager/SSMS order everything by name. Nothing like having to scroll through thousands of SP's to find the right ones - yes, I know how to filter the list but there are tools (e.g. visual studio) that don't filter like SSMS.
Joe
January 31, 2008 at 9:12 am
Format I use is usp (for User Stored Procedure) + Project Name _ App Name _ Function
example: uspAP_MonthlyReconcile_Update (Insert, Delete, etc.)
January 31, 2008 at 9:25 pm
I'm with James except that I don't bother prefixing with sp, usp or anything 🙂 I have procedures such as
TC_Sheets_Get
which is for the theatre costing system to retrieve one or more sheets.
I think those who argue against sp_ are those who have DBs with sp_ at the moment and don't want to feel bad 😀 It's fair enough - if I bought a brand new and expensive car and then someone told me I could've researched more and bought a cheaper and better one I'd still argue that mine's better even if I secretly know it's not 🙂 Silly comparison but you get the idea.
February 4, 2008 at 9:33 am
One other thought on converting from sp_ to some other prefix like usp_. If you use sp_ there could be times when you (or someone else) can get unexpected results from the stored procedure. If you remember this discussion then it could be just a simple fix. If not, you may end up spending hours, maybe even days or weeks trying to figure out just what is going on.
Save yourself the frustration (and maybe even some time) by converting over time.
Steve
November 8, 2008 at 9:18 am
dear ,
i have to check ur code in sqlserver200 ,
but sp_xyz not running in Master Table
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply