Re: Stored Procedure Naming?
Recently we have started grouping things belonging to one application using the schema in SQL 2005. So we use what you have stated in example1 like, myAppName.GetDropDownValues.
We have some databases that have been ported from 2000 and there we are stuck with a weird mix of naming conventions(some use old convention and a few applications have started using schema). The old convention that we follow is: pr_AppName_[Get/Set]_MySPDoesThis (I somehow like the pr_ over usp_ :D) The only problematic thing that happens here is the SP name abbreviation. Each developer has their own choice of abbreviating and at times that becomes difficult for others to comprehend what that means.
Re: Stored Procedure Naming?
Quote:
Originally Posted by
RobDog888
[color=navy]What techinques do you guys use when naming stored procedures?
... snip ...
And getting out of hand example: 3
GetAllNotesAndActivityForACustomerByCustomerKeyBySortedNoteTypeAndDateRangeIfTheyHaveAnyNotesOrActiv ityAtAll
That has happened in our shop.
The technique I suggest you use is that you stop trying to make an application out of the database! We have had major performance problems to solve because some people (who are no longer with us) decided that stored procedures were the answer to EVERYTHING.
Try the n-tier approach, it works.
-Max :D
Re: Stored Procedure Naming?
3000 stored procedures. :eek:
I usually consider how they would be sorted in most common places where they would be displayed like Management studio treeview/listview etc. This helps keep things neat & tidy as things related to one application etc. would all be displayed at one place rather than scattered.
To do this I go from broad to specific. Like first usp.. then application name.. then procedure name etc.
e.g.
sql Code:
usp_AppName_ProcName -- User stored procedure specific to one application
usp__ProcName -- User stored procedure generic to more than one applications
Re: Stored Procedure Naming?
Quote:
And getting out of hand example: 3
GetAllNotesAndActivityForACustomerByCustomerKeyBySortedNoteTypeAndDateRangeIfTheyHaveAnyNotesOrActiv ityAtAll
I would prefer to eleminate this option alltogether by passing appropriate parameters to the stored procedure like select criteria, sort field etc. Besides reducing the number of stored procedures in my database, it is also useful if we need to change something. We would need to change just one stored procedure as opposed to say 10 odd ones that return same/similar resultset with minor differences like select/sort fields etc.
Re: Stored Procedure Naming?
maybe something like this:
sql Code:
CREATE PROCEDURE usp_Customer_GetAllNotes
@CustomerId int = NULL,
@CustomerName int = NULL,
@SortField varchar(20) = NULL
AS
BEGIN
SELECT Customers.*
FROM Customers
WHERE (@CustomerId IS NULL OR CustomerId = @CustomerId)
AND (@CustomerName IS NULL OR CustomerName = @CustomerName)
ORDER BY CASE @SortField
WHEN NULL THEN 1
ELSE @SortField
END
END
Re: Stored Procedure Naming?
up_WhatItDoes
Because the databases are shared across applications and they can also use the same SP. It'd be redundant to have two SPs with the same code in it.
Re: Stored Procedure Naming?
Good feedback.
Our main production sql database is 1TB is size and we have many databases and environments. Its a nightmare to maintain, glad its not me lol.
Our apps all share most sp's and performance it the number #1 priority as we have maximium response times allowed under contract with our retailers and partners, some as little as 1 sec max, so to make the sp's all multi-use will degrade performance.
But the run-on description naming of a sp is our problem. How descriptive should a sp be?
Yes froggy a description is best but how detailed. Ours seem to most describe the function and the parameter(s) passed like "...ByCustomerKey" but if we have 5 different sp's all the same with different passed in parameters then there has to be some distinction between them.
Re: Stored Procedure Naming?
we use pr_[app code name]_[sub-system name]_[data]_[action]
So it looks something like htis:
pr_ERP_Customer_Details_Get <- for getting details of a customer
or
pr_ERP_Customers_Get <- for getting a list of customers
shared sprocs are put in the "Library" system:
pr_Library_Defaults_get
-tg
Re: Stored Procedure Naming?
I personally disagree with putting the app name on because it represents a step away from code re-use. On that basis I'd go with naming them by function. We go with something like Customer_Get, Order_Set etc
We use the same technique as Pradeep to eliminate the need for different sprocs for every possible search combination in our gets. I've yet to see it cause performance problems if all the possible parameter combinations can be handled by a single query's where clause. If, on the other hand, you find yourself branching the execution in a sproc using IF statements then you want to start considering creating a separate sprioc.
Even eliminating the multi-parameter permutations, though, we do still have some really unwieldy names. The problem isn't so much in the parms the sproc recieves but in the fields it returns. The columns we want to display in one part of the system are different to the columns we want to display in another part. That gives us a choice of having one sproc that returns every possible column (very wasteful) or lots of sprocs that return specific column sets (massive code bloat and a need for run on names). In practice we end up doing something in the middle. I've yet to come up with a solution to that problem that I'm really happy with.