SSW SQL Stored Procedure Naming Standard

Home > SSW Standards > Developer SQL Server > SQL Server Standard Stored Procedure Naming

See what people think about this product

I've been putting together Development Guidelines for my employer and in the process have reviewed many published standards (in the .Net arena) from around the world. In each category, the suggestions at SSW are always among the best.

- Leon Bambrick,

SQL Server Stored Procedure Naming Standard

This standard outlines the standard on naming Stored Procedures within SQL Server. Use these standards when creating new Stored Procedures or if you find an older Stored Procedure that doesn't follow these standards within SSW.

Note: Stored Procedures will run fractionally slower if they start with a prefix of sp_ This is because SQL Server will look for a system stored proc first. Therefore we never recommend starting stored procs with a prefix of sp_

Do you agree with them all? Are we missing some? Let us know what you think.

Syntax

Stored Procedure names are to have this syntax:
[proc] [MainTableName] By [FieldName(optional)] [Action]
[ 1 ] [ 2 ] [ 3 ] [ 4 ]

[1] All stored procedures must have the prefix of 'proc'. All internal SQL Server stored procedures are prefixed with "sp_", and it is recommended not to prefix stored procedures with this as it is a little slower.
[2] The name of the table that the Stored Procedure accesses.
[3] (optional) The name of the field that are in the WHERE clause. ie. procClientByCoNameSelect, procClientByClientIDSelect
[4] Lastly the action which this Stored Procedure performs.

If Stored Procedure returns a recordset then suffix is 'Select'.
If Stored Procedure inserts data then suffix is 'Insert'.
If Stored Procedure updates data then suffix is 'Update'.
If Stored Procedure Inserts and updates then suffix is 'Save'.
If Stored Procedure deletes data then suffix is 'Delete'.
If Stored Procedure refreshes data (ie. drop and create) a table then suffix is 'Create'.
If Stored Procedure returns an output parameter and nothing else then make the suffix is 'Output'.

Examples

An example of a stored proc that returns only an output parameter

ALTER PROCEDURE procClientRateOutput

         @pstrClientID VARCHAR(6) = 'CABLE',
         @pstrCategoryID VARCHAR(6) = '<All>',
         @pstrEmpID VARCHAR(6)='AC',
         @pdteDate datetime = '1996/1/1',
         @curRate MONEY OUTPUT

AS

-- Description: Get the $Rate for this client and this employee
-- and this category from Table ClientRate

SET @curRate = (
                SELECT TOP 1 Rate
                FROM ClientRate
                WHERE ClientID=@pstrClientID
                AND EmpID=@pstrEmpID
                AND CategoryID=@pstrCategoryID
                AND DateEnd > @pdteDate
                ORDER BY DateEnd
               )

IF @curRate IS NULL

         SET @curRate =
(
                SELECT TOP 1 Rate
                FROM ClientRate
                WHERE ClientID=@pstrClientID
                AND EmpID=@pstrEmpID
                AND CategoryID='<ALL>'
                AND DateEnd > @pdteDate
                ORDER BY DateEnd
               )

RETURN

Old Style	Recommended Style
Select 'procGetRate' or 'sp_GetRate'	'procClientRateSelect'
Insert 'procEmailMergeAdd'	'procEmailMergeInsert'

Acknowledgments

Adam Cogan