Stored Procedures
1. All application level and user defined stored procedures are prefixed with the constant 'usp_' with a description of what the stored procedure does. Example: usp_GetLastModifiedDate.
2. Use upper case for all SQL keywords: e.g., SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.
3. Liberally comment code blocks not easily understood by non-technical persons.
4. Use single-line comment markers (--) for comments and headers.
5. Reserve multi-line comments (/*.. ..*/) for blocking out sections of code.
6. Indent SQL code 4 spaces per appropriate line to improve readability.
7. Use single quote characters to delimit strings.
8. Use parentheses to increase readability: e.g., WHERE (Color=’red’ AND (Size = 1 OR Size = 2)).
9. Use BEGIN…..END blocks when multiple statements are present within a conditional code segment.
10. Use one blank line to separate code sections.
11. Use spaces so that expressions read like sentences;
e.g., fillfactor = 25, not fillfactor=25.
12. Place SET statements before any executing code in the procedure.
13. Use SET NOCOUNT ON at the beginning of stored procedures.
14. Tables, views, and stored procedures are always fully qualified to ensure SQL Server follows a direct path to find it rather than searching through the master catalog.
15. Do not call functions repeatedly within your stored procedures, triggers, functions and batches. For example, you might need the length of a string variable in many places of your procedure, but don't call the LEN function whenever it's needed. Instead, call the LEN function once, and store the result in a variable for later use.
Example:
