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' |