Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Stored Procedure Checklist

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)

Comments

Posted by Anonymous on 11 November 2011

Pingback from  Dew Drop – 11/11/11 | Alvin Ashcraft's Morning Dew

Posted by Bill Talada on 21 November 2011

I generally check many more things such as parameters that match column names should match types and sizes.  ANSI_NULLS causes problems so I search for that.  Weird line endings such as LfLf instead of CrLf break a bunch of my code processors too.  I look for "CREATE TABLE" and cursors too.  I look for weird formatting too such as three blanks and replace with tabs.  Spaces before line endings bug me too. Anything deprecated, I search for such as != instead of <>.

Posted by dan.latvala on 21 November 2011

I always try to remember to add the permissions to the bottom of the copy of the SP that goes into source control.  It saves you from having to remember to add them if you are re-creating the SP in a staging or production environment.  Just a simple GRANT EXECUTE ON <SP> TO <Groups>.

Posted by mtassin on 21 November 2011

When did != get depricated?  I can't find any documentation to that being true.

I did hear somewhere that dropping #temp tables was actually bad form because SQL has started caching table definitions, and the drop would cause the CREATE #TABLE to be recompiled or something like that.

Posted by HLogic on 21 November 2011

!= is not ANSI.  

CREATE #table should be at the top of the SP to minimize recompiles.

Posted by tom.groszko on 21 November 2011

In newer versions of SQL server dropping temp tables is a bad idea. If the definition is in the cache it can be reused. If you don't drop it the server in it's own good time will drop while dropping others (likely more efficient than one at a time) and your stored procedure will not have to wait for this to happen.

Posted by Anonymous on 21 November 2011

Pingback from  Stored Procedure Checklist | SQL Server | Syngu

Posted by heino.zunzer on 22 November 2011

Some additional things checked here:

- set nocount on

- transaction handling: should be done on application level. so if it's in the SP, what's the reason for that?

- return values and raised errors documented

- grants to the proc in the same script

Posted by Dave Vroman on 22 November 2011

Variables / non-keywords opposite of your standard case (upper / lower). My preference is keywords upper and non-keywords camelcase. This makes decoding the code much easier.

Leave a Comment

Please register or log in to leave a comment.