I've included a few articles that recommend similar but slightly different SP naming standards:
SSW SQL Stored Procedure Naming Standard [proc] [MainTableName] By [FieldName(optional)] [Action] e.g procClientRateSelect'
Practical Methods: Naming Conventions
Prefix all stored procedures with "p". Complete the name with the primary table affected, then the job performed. This will group all procedures for a given table in one location alphabetically.
Samples: pCustomerList, pCustomerSearch, pCustomerCreate, pCustomerRead
Naming database objects.
Examples: carrier_get_proc, group_client_add_proc, employee_transfer_proc
Stored procedures should be named as: name_action_proc.
And finally I found the one that I consider is the more convinient(Sidebar "Database Naming Conventions" in Common .NET Naming Conventions)
When you name your procedures use "by" for the sort order, and "for" for criteria and start them off with the logical object name that the procedure works with. This makes it much easier to find specific procedures when you have a whole pile of them in a particular database. I also see no real reason for prefixes like "usp_" as all they do is add more characters to the name. Keep it simple, useful, and informative.
Examples : CustomerGetSingleForID,CustomerGetListAllByName,OrderGetListForCustomerIDByDate , CustomerUpdate,CustomerDeleteSingle, CustomerInsertSingle
Some people put an underscore after the object name in the stored proc: Customer_UpdateSingle. That looks fine to me as well. Either way, all the procedures related to a logical entity show up in the same location in the listings and trees in the various SQL Server management programs and add-ins.
http://www.irritatedvowel.com/Programming/Standards.aspx is an excellent article that describes general naming standards that I recommend to follow.
I use some exceptions-additions to the rules:
Enumerations- sometimes it is more clear to add Enum to the name of enumeration,e.g ModeEnum instead of just Mode
Hungarian Notation in local, member variables and parameters.
Often it easier to recognize the purpose of variable by adding prefix s (for string)or b(for boolean). In some functions there are parameters of object type, that are converted to string variables and prefix helps to distinguish them.