Stored Procedure Naming?

[RobDog888]

What techinques do you guys use when naming stored procedures?
I like to go by function but it was brought up by an Architech that a sp we are working on should be appended by application name.

We have over 3000 sp's here in just one database and by funciton really helps to find sp's for use in your app without having to accidentally rewrite a sp that already existed.

Example 1:
GetAllNotesForACustomerByCustomerKey

Example 2:
GetAllNotesForACustomerByCustomerKeyForApplicationXYZ

And getting out of hand example: 3
GetAllNotesAndActivityForACustomerByCustomerKeyBySortedNoteTypeAndDateRangeIfTheyHaveAnyNotesOrActiv ityAtAll

I like to also append "usp_" before the name to help filter user created sp's from system.
Example 1 is my preference.

Even if example 2 is specific to a single app, there is no real realson to add an applications name to teh end IMO.

Which do you guys do?

[rjv_rnjn]

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_ ) 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.

[Max Peck]

[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

[Pradeep1210]

3000 stored procedures.

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.

usp_AppName_ProcName          -- User stored procedure specific to one application
usp__ProcName            -- User stored procedure generic to more than one applications

[Pradeep1210]

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.

[Pradeep1210]

maybe something like this:

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

[mendhak]

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.

[RobDog888]

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.

[techgnome]

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

[FunkyDexter]

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.