//
you're reading...
SQL Server

T-SQL Stored Procedure Tips

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.

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: