DBAs find it easier to manage large Database Server environments when checklists are used. This Stored procedure checklist  will stimulate some ideas you could use

For acceptance into the SQL Server environment , I’ve developed some scripts that scan the T-SQL code for some items in this checklist

       1)  Same case for T-SQL Keywords. I prefer UPPER CASE , but I know other DBA\Developers like lower case.

      2)  Inspect input parameters for valid values (numbers, strings etc)

      3)  Force developers to justify  usage of  dynamic SQL (SP_EXECUTESQL, EXEC('SELECT col1…..')

      4)  Commenting. I prefer a comment block at the top with usage and revision history . Snippets throughout the code to enhance the understanding

      5)  Limit xp_cmdshell usage

      6)  Think in set theory. Avoid looping  such as cursors.

      7)  Clean up . Close objects ,drop #temp tables, deallocate\close cursors, close transactions

      8)  Use try...catch blocks. Trap errors and report into a log file

     9)  Performance is satisfactory

    10) Investigate input parameters for SQL injection attacks. Better if it occurs at the application level .

     11)  Manage  security. Follow the SQL Server Security Policy.Access to stored procedure and underlying objects

Author: Jack Vamvas (http://www.sqlserver-dba.com)