Stored Procedure Naming Convention

[Terpsfan]

I've read different books on creating stored procedures and I'm starting to get confused. I've read that you should never preface a stored procedure with sp so that it won't be confused with system stored procedures. I've seen sps for select procedures, spi for inserts...some say you preface the name with proc, etc. Just would like some feedback.

 

[suenram]

This is one area that starts out simple and grows complex as you get many, many stored procedures. I started by following this recommendation:

http://vyaskn.tripod.com/object_naming.htm

I believe Ins/Upd/Del/Sel should be in the name as well as having them in some sort of logical grouping so you don't have to hunt all over the place. Of course, some Stored Procedures have multiple actions and then you get into the question of how much detail must there be in a name. It is always good to start a new project with some sort of convention and then stick with it. Having an expert come in and give you a 'new' naming convention in the middle of a code release always causes problems.

I have tried to leave 'sp' for Stored Procedures in the Master Database only. Suenram

 

[pankajv]

We have following naming convention:

All stored procedures will have a prefix "USP_", Followed by a 3 word suffix to identify the type of operation the stored procedure is going to perform
Sel - Select
Ins - Insert
Mod - Update
Del - Delete

For e.g.

USP_SelAllDetails
USP_InsAllDetails
USp_ModAllDetails
USP_DelAllDetails

You can decide your own naming convention for stored procedure which does multiple operations.

 

[topher0303]

You should not prefix sprocs with sp_ from a performance standpoint. When executing a sproc with sp_, it first looks in the main database, if not found, it then looks in the current database. If it finds it there, it will always recompile it before executing it, which defeats the main reason for sprocs. Also, if your sproc is named the same as one in the main database, the one in the main database will be executed.

As for any naming conventions beyond this, it is just preference.

 

[tlbroadbent]

By "main database," topher0303 means the master database. You can avoid the problem mentioned by fully qualifying the SP name when executing it (exec mydb.dbo.sp_myproc), but I concur it is best to avoid using "sp_" as the first 3 characters of the procedure name. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[topher0303]

Thanks for the correction tlbroadbent. Been a few months since I have dealt with SQL Server and I appear to be a little rusty on my names.

As for using the fully qualified sproc, I have read that is best once before as well. However, wouldn't this cause a problem if you create a second copy of the database on the same server (which seems to happen in dev), the second copy would actually execute the stored procedure in the first copy? I was always curious about that one but never get around to researching it.

 

[tlbroadbent]

You're right about the possibility of executing SPs in the wrong database if you use the full name. That's another reason to have development databases on a different server or SQL Server instance. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[SQLSister]

I see alot of us are simliar in how we name our stored procedures. We start with USP (for user stored procedure) then the indicator of what it does (insert, select, etc) and then the module of the program, web page and/or some phrase which indicates the functionality of the program (since not all relate to a specific module or webpage). So the program which performs te aircraft search would be USP_SEL_AIRCRAFTSEARCH.

Sometimes this makes for long names, but when you are trying to maintain hundreds or thousands of stored procedures, it sure makes it easy to find the right ones!

 

[topher0303]

SQLSister, I use very similar naming. However, I usually describe what I am returning, such as usp_GetAllUsers, usp_GetUserForID, usp_GetUsersForDept, usp_AddUser, etc. The only exception is when I write a sproc that returns XML using the FOR XML clause, i prefix with SPXML_ (i.e. spxml_GetAllUsers).

 

[SQLSister]

Oh yeah, I forgot to add that if I'm doing something in development as an experiment or to test something else, I always put Test in the name. That way I know not to move it to production.