Skip Navigation LinksHome > SSW Standards > DeveloperSQLServer > SSW SQL Stored Procedure Naming Standard

There was an error displaying the testimonials. Please report this error to SSW and include the following text:
- A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

 

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