10 Things you should know when writing stored procedures
1) Same case for T-SQL Keywords. Upper case is the generally accepted practice, just be consistent.
2) Inspect input parameters for valid values (numbers, strings etc)
3) Use dynamic sql only when justified and absolutely necessary i.e. (SP_EXECUTESQL, EXEC(‘SELECT col1…..’)
4) Commenting. Use comments profusely, they do not cost you anything. Use a comment block at the top with usage and revision history and snippets throughout the code to enhance the understanding.
5) Limit or eliminate xp_cmdshell usage – Extremely dangerous!
6) Use Sets. Avoid looping operations such as cursors or RBAR(Row by Agonizing Row). If Set theory is new to you good article here: http://sqlmag.com/t-sql/t-sql-foundations-thinking-sets
7) Clean up . Close objects ,drop #temp tables, de-allocate\close cursors, close transactions
8) Handle Errors. Use try…catch blocks. Trap errors and report into a log file
9) Investigate input parameters for SQL injection attacks.
10) Manage security. Use stored procedures and views to control user access to data instead of letting them directly access tables.