How should we name the stored procedure?

As the project goes bigger and bigger, many enhancements were done to the existing systems, you might find out that the programmer must have discipline in naming the stored procedure and other database objects. Otherwise, the system maintenance is going to be a painful job.

Let me explain a bit on the stored procedure naming convention:

  "pr_" + "module initial" + "sub module initial" + "process name"

• The stored procedure name starts with "pr_" instead of "sp_" where "sp_" is meant for MSSQL system stored procedure. Basically, "pr_" stands for "procedure".
• "module initial" is the second section in the stored procedure name where it is short code or initial of the module name.
• "sub module" is the third section in the stored procedure name and it denotes the short code or initial for the sub-process.
• Lastly, "process name" is the actual process to be implemented within this stored procedure.

For example,

• pr_pos_rpt_sales - the module name is "pos" (i.e., Point Of Sales) and sub module name is "rpt" (i.e., Report). The "sales" means that it is sales data related process. From the stored procedure name, it is intuitive and it simply means that this stored procedure is handling the POS sales report generation.
• pr_ic_process_trans - the module name is "ic" (i.e., Inventory Control), the sub module name is "process" (i.e., process related) and "trans" is the short name for "transaction". This stored procedure is responsible for processing the inventory transactions.

For other database objects,

• "tb_" is for the table object.
• "fn_" is the function.
• "vw_" is the view.