So, I made some quick notes about how I name (some of) my stored procs in a project I'm working on. These notes are pretty rough, they're really only for my reference..
John.
sproc rules:
- An error level is returned by all sprocs.
- Sprocs do not manage transactions, but will stop processing on first error skipping all other logic and returning the error number (in addition to the RAISE ERROR).
- All sprocs must take a token as the first parameter.
- Variables intended to be used as constants are declared and initialized first
- @tk is the standard for a guid identifying a user session (the token)
- @uid is the standard for an integer User ID.
- @err is the standard for an integer to track the error level.
- @sc is the standard for an integer representing a column ordinal for sorting
- @sd is the standard for a bit representing the sort order (sd = sort descending)
- @rc is the standard for an integer representing a record count.
All custom sprocs start with _. All application/admin sprocs start with __.
Custom sproc grammar:
Adhoc:
Always returns a DataSet. No side-effects. Can have any number of output variables.
syntax: Adhoc_TABLE_DESCRIPTION
Routine:
Does not return rows. Can alter state. Can have any number of output variables.
syntax: Routine_TABLE_DESCRIPTION
Calculate:
Does not return rows. Does not have side-effects. Can have any number of output variables.
syntax: Calculate_TABLE_(LABEL_)[FOR _ BY]
Count:
Does not return rows. Does not have side-effects. Can have any number of output variables. Output variable @result returned as Int32.
syntax: Count_TABLE_FOR
Delete:
Does not return rows. Removes records. Can have any number of output variables.
syntax: Delete_TABLE_FOR|BY
Retrieve:
Returns one record set. Does not have side effects. Can have any number of output variables.
syntax: Retrieve_TABLE_[LABEL_]FOR|BY
Update:
Does not return rows. Updates records. Can have any number of output variables.
syntax: Update_TABLE_DESCRIPTION
NOTES:
TABLE is any valid table name. (Even if it operates on a related or multiple views exclusively)
DESCRIPTION is any string. Underscores will be removed. Describes the procedure.
LABEL is any string without underscores. Describes the item to its left (a variable or table depending on location).
FOR syntax: For_LABEL(_And|Or_LABEL)
BY syntax: By_LABEL(_And|Or_LABEL)