Stored Procedure Naming Convention

Do you know the naming convention for the stored procedure? If not, no worries. Follow this simple and easy tutorial to see the rules for naming conventions in stored procedures.

Table of Contents

Stored Procedures in SQL Server

A stored procedure consists of one or more statements that perform specific tasks. They usually include statements that are used repeatedly. Programming functions and stored procedures are comparable in that stored procedures can take parameters and execute operations when called upon.

Using a stored procedure, you can perform the following actions,

Insert
Delete
Update
Select
Get
Validate

Choosing Prefix

“sp_” is the prefix that makes the most sense for storing procedures. However, since SQL Server already uses it as a standard naming convention in the master database, there’s at least one solid reason to avoid it.

If you don’t tell SQL Server which database contains the object, it will first check the master database to see if it’s there before moving on to the user database.

If you decide to switch, you should probably avoid using this as a naming convention even if you do not host your database(s) on SQL Server.

Examples of the stored procedure,

usp_insert_person
usp_delete_person
usp_update_person
usp_select_person
usp_get_person
usp_validate_person

User Defined Function

A user-defined function is similar to built-in database functions in that it only takes input parameters and consists of a series of SQL statements that perform operations and return the result, which can be a table or just one value.

User-defined functions must be named with the prefix “fn_” followed by the function’s action. Therefore, the syntax should resemble stored procedures.

Some examples of function names would include:

fn_count_string_instances
fn_get_customer_balance
fn_is_inventory_in_stock
fn_get_column_type

Views

In a database, a view is a “virtual table” specified by a query. A view can contain only a portion of the data or, via joins, combine data from two or more tables. Therefore, views are useful for abstracting or hiding challenging questions.

A view’s namespace, results, and “v_” or “vw_” prefix should come first in naming conventions. Therefore, the syntax will be:

vw_sales_by_store
v_staff_list
v_sales_by_product_category

Benefits of Naming Convention

The advantages of naming conventions are listed below.

It gives all objects a unique name, ensuring they have the same appearance or belong to the same group.
After a long period, less work is required to read and comprehend source code.
Clarifies the name of the action that any object will perform.
Improves clarity when there may be confusion.
Gives those reading all objects for the first time a better understanding.
Sorting objects so that similar objects stay in order reduces the time it takes to find a specific one in a database.

Indexes

The database search engine can retrieve data more quickly by using indexes, which are unique lookup tables. The index naming convention is said below.

The format IX__ should be used for each index name.
“IX_” should be the prefix added to each index name.
Capital letters should appear at the beginning of each table and column name.
The final letter “s” (or “es”) in the table name is designated plural.
When more than one word appears in the index name, the first character of each word should be capitalized and separated with an underscore (_).

Example

IX_Employees_EmpId
IX_OrderDetails_OrderNo_OrderDate

Points to Consider During Naming Convention

For any object name, do not use predefined SQL keywords.
If necessary, omit spaces from the names of any objects enclosed in brackets ([]).
An object’s naming convention should be consistent; don’t use different conventions for the same kind of object.
Disregard using special or numeric symbols in the naming, such as (tbl_Employee@Master, tbl_Employee3 ).

Conclusion

Not every database object needs rules applied to it. One option is to limit the application of naming convention rules to table and column names. Adopting a naming convention is optional but advantageous when creating a stored procedure.