SQL Server Stored Procedure Coding Standard

Must read before start writing stored procedure in SQL server 2005

Best practices

• capitalize reserved words
• main keywords on new line
• can't get used to commas before columns
• always use short meaningful table aliases
• prefix views with v
• prefix stored procs with sp (however don't use "sp_" which is reserved for built in procs)
• don't prefix tables
• table names singular
• Use the brackets around objects, so the query engine excplicitly knows a field when it sees it

• Use THE SAME CASE as table object names and field names
• When calling SPs from application, use the fully qualified [dbo].[procName] with correct owner AND case. Not Kidding! Read the articles above!
• Reference the owner of the object so security is explicitly known and doesn't have to be figured out
• Use SET NOCOUNT ON and SET NOCOUNT OFF to eliminate the extra overhead to keep track of how many records are updated in the stored proc unless you need them. Normally, you don't and you can gain a huge increase in performance.

Preferences

• Prefix stored procs with proc
• Suffix every stored proc with SEL, UPD, DEL, INS (or SELECT, UPDATE, DELETE, INSERT)
• Capitalize reserved words
• Main keywords on new line (scripting)
• Use commas before columns (scripting)
• Prefix views with vw
• Don't prefix tables
• Table names singular
• Add a suffix to the standard names like "_ByPK", "_OrderByLastName", or "_Top15Orders" for variations on the stock SP

Example 

    SELECT

      column1
    , column2
    , column3
    , COALESCE(column4,'foo') column4FROM
    tablenameWHERE
    column1 = 'bar'
ORDER BY 
      column1
    , column2

Do

1. Write SET NOCOUNT ON in top of the procedure
2. Every SELECT statement should return with WITH NOLOCK key word.
3. Procedure should be rerunnable, Use IF EXISTS statement …
4. procedure always should have return value or return parameter
5. Use output parameter wherever necessary
6. Each code (for loop, If else etc) should be in BEGIN .. END block. For conditional checks( if else), please follow the same.
7. Use 'Derived tables' wherever possible, as they perform better. Consider the following query to find the second highest salary from the Employees table:

SELECT MIN(Amount) FROM Department WHERE DeptID IN

( SELECT TOP 2 DeptID FROM Department ORDER BY

Amount Desc)

The same query can be re-written using a derived table, as shown

below, and it performs twice as fast as the above query:

SELECT MIN(Amount) FROM

( SELECT TOP 2 Amount FROM Department ORDER BY

Amount DESC) AS D

Don’t

8. Do not include any business logics in your stored procedures, build a Business Logic Layer for that. Keep to what SQL was made for, inserting, deleting and selecting data.
9. Do not use reserved words, if you can’t avoid the use square brackets like: [event]
10. Do not use ‘SP_’ or ‘SYS_’ as a prefix in the stored procedure name.
11. Do proper Error handling in the procedure : use sp_addmessage to create user defined messages and raiserror for raising and passing the error code and description back to calling application.
12. Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that results in an index scan, which defeats the purpose of an index. The following statement results in an index scan, while the second statement results in an index seek:

SELECT Ename FROM Employee WHERE Ename LIKE '%han'

SELECT Ename FROM Employee WHERE Ename LIKE 'A%n'

Also avoid searching using not equals operators (<> and NOT) as they result in table and index scans.

13. Avoid the creation of temporary tables while processing data as much as possible, creating a temporary table means more disk I/O. Consider using advanced SQL, views, SQL Server 2000 table variable, or derived tables, instead of temporary tables.

14. Do not use SELECT * in your queries. Always write the required Column names

after the SELECT statement, like:

SELECT CustomerID, CustomerFirstName, City

This technique results in reduced disk I/O and better performance.

15. Do not wtite insert statement without defining the column name like below

INSERT INTO LMS_ERROR_LOG VALUES

( @pProspectId, @pMethodName, @pErrorMsg,

getdate() )

Always define the column name in the insert statement like below

INSERT INTO LMS_ERROR_LOG

( PROSPECT_ID,METHOD_NAME, EXCEPTION,DATE_TIME ) VALUES

@ProspectId,@pMethodName,@pErrorMsg,getdate() )

16. Do not write multiple declare statement inside the procedure like

Declare @Fname nvarchar(100)

Declare @Mname nvarchar(100)

Instead of that you can write like below

Declare

@Fname nvarchar(100),

@Mname nvarchar(100)

I normally follow below guidelines too for consitancy

17. Stored procedure name should be in CAPS latters for consistancy
18. Do Proper indenting ( 2 tabs for start and same for next levels)
19. Stored procedure name should follow some standrad to identify as below PROJECT_NAME_<SELECT OR INSERT OR UPDATE OR DELETE>_<OPERATION or TABLE_NAME>
20. Global parameter start with "p" and local parameter start with "v" . and for OUT parameter name should @pOut<ParameterName>
21. Created By/Date and purpose must be in the procedure as comment
22. All key word should be in the CAPITAL latters