What Is Your Naming Convention for Stored Procedures

What is your naming convention for stored procedures?

For my last project i used usp_[Action][Object][Process] so for example, usp_AddProduct or usp_GetProductList, usp_GetProductDetail. However now the database is at 700 procedures plus, it becomes a lot harder to find all procedures on a specific object. For example i now have to search 50 odd Add procedures for the Product add, and 50 odd for the Get etc.

Because of this in my new application I'm planning on grouping procedure names by object, I'm also dropping the usp as I feel it is somewhat redundant, other than to tell me its a procedure, something I can deduct from the name of the procedure itself.

The new format is as follows

[App]_[Object]_[Action][Process]

App_Tags_AddTag
App_Tags_AddTagRelations
App_Product_Add 
App_Product_GetList
App_Product_GetSingle

It helps to group things for easier finding later, especially if there are a large amount of sprocs.

Regarding where more than one object is used, I find that most instances have a primary and secondary object, so the primary object is used in the normal instance, and the secondary is refered to in the process section, for example App_Product_AddAttribute.

Naming convention for parameters in stored procedures in postgresql

There is nothing established conventions but there are 2 common themes I seen. But first on either do not truncate parameter names this actually makes reading and understanding them harder; also allign them instead of just stringing them out. The most common is to preference the parameter as p_ . The other is to suffix parameter with its usage an _in or _ot
Examples:

CREATE PROCEDURE 
   insert_assignment(p_student_id INT, p_period INT, p_cl VARCHAR(50), p_ln VARCHAR(50), p_sped_id INT, p_assignment VARCHAR(500))
LANGUAGE SQL ...

or 

CREATE PROCEDURE  
   insert_assignment(student_id_in INT, 
                     period_in INT,
                     cl_in VARCHAR(50),
                     ln_in VARCHAR(50), 
                     sped_id_in INT,
                     assignment_in VARCHAR(500)
                    )
    LANGUAGE SQL ... 

Not sure about cl,ln as you did not use them. As for sepd if it is in common use in your organization it is fine otherwise (IMHO) it is a poor name. Make understanding your primary goal, not less typing. (and this from a very poor typist)

Avoid Naming User Stored Procedures SP% or SP_%

The reserved prefix that exhibits this behaviour is sp_. Not sp.

A stored procedure called spAddUser will be resolved in the normal way without looking for a matching object in master.

The relevant quote in books online is

Avoid the use of the sp_ prefix when naming procedures. This prefix is
used by SQL Server to designate system procedures. Using the prefix
can cause application code to break if there is a system procedure
with the same name.

But I would avoid these prefixes anyway. If all the stored procedures are prefixed sp it quickly gets annoying IMO.

Oracle parameter naming conventions

No. Identifier must start with a letter.

---

EDIT :
Of course, you can use quoted identifiers with @ inside if you like it very much:

function f("@parameter" int) return int is
begin
   return "@parameter" + 1;
end;

Naming conventions for Data Access Layer especially in case of Stored Procedures

I would be tempted to remove the DASH from the front of the procedures. In general procedure names should reflect what the procedure does, not who is using it.

Consider the extreme case:

• get_all_customers

vs

• get_all_customers_for_dashboard
• get_all_customers_for_invoice_background_job
• get_all_customers_for_ad_hoc_report
• ...

The fact that your stored procedures are used by a background part of your dashboard application is nothing to do with the procedures themselves, and naming them as such violates encapsulation.

If you wrote another application that needed the same logical queries, would you write new procedures prefixed by APPNAME?

Oracle stored procedure variable naming convention

Consistency is key to naming conventions.

It doesn't matter so much what you use, as long as the convention is always used. After that, documentation if necessary.

That said, one convention I liked was to indicate the direction in the variable name. IE: An IN parameter would be: IN_FIRST_NAME; an OUT parameter would be: OUT_FIRST_NAME, and an IN OUT would be IN_OUT_FIRST_NAME. Oracle has character limits (32 characters IIRC), so don't get too crazy.

---

Related Topics