Easy understanding of naming standards for SQL Server Stored Procedures

In this article, the naming method of the standard blueprint is only applicable to SQL. If you are creating a new stored procedure or find a stored procedure that is not constructed according to this standard, you can refer to this standard.

 

Note: If the stored procedure is named with the prefix SP _, it runs slowly. This is because SQL Server first searches for the system stored procedure, therefore, we do not recommend SP _ as the prefix.

The stored procedure name includes the following syntax:

 

[proc] [MainTableName] By [FieldName(optional)] [Action] [ 1 ] [ 2 ] [ 3 ]　　[ 4 ]

(1) All stored procedures must have the prefix 'proc'. All system stored procedures have the prefix "SP _". We recommend that you do not use this prefix because it slows down a little.

 

(2) The table name is the object accessed by the stored procedure.

 

(3) optional field names are condition clauses. For example:

 

procClientByCoNameSelect, procClientByClientIDSelect

 

(4) The final behavior verb is the task to be executed in the stored procedure.

 

If the stored procedure returns a record, the suffix is: select

 

If the Stored Procedure inserts data, the suffix is insert.

 

If the stored procedure updates data, the suffix is: update.

 

If the stored procedure is inserted or updated, the suffix is: Save

 

If the Stored Procedure deletes data, the suffix is: Delete.

 

If the stored procedure updates the data in the table (ie. Drop and create), the suffix is: Create

 

If the stored procedure returns an output parameter or 0, the suffix is output.

Example:

A stored procedure that returns only one 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

Previous writing:

 

Select 'procGetRate' or 'sp_GetRate' Insert 'procEmailMergeAdd'

Current statement:

 

'procClientRateSelect' 'procEmailMergeInsert'