Sql Server code template & guideline

A Sample structure to follow in all SQL database code like procedure, view, function, etc. These are samples not applicable to all developers and teams. It will help when the team is too big and the build release process is too frequent. Some times the production environment is too sensitive or limited permission on code executions like some time security purpose admin restrict drop and create privilege on objects.

/******************************************************************
* DATABASE: OMAPINTERNET
* PROCEDURE NAME: usp_SelectAllResults
* DATE: 04/03/2009
* AUTHOR: Homer Simpson
* PROCEDURE DESC: This proc returns all the results for a specific NDC,
* Work Order# 13798
*******************************************************************
* DATE:         Developer        Change
*----------     -------------    -------------------------
*08/01/2013     Himanshu Patel   Add Indicator
******************************************************************/


Follow rule when writing procedure

  • Must-Have camel case name
  • Must have Error handling (try /catch )
  • Avoid use Cursors
  • Indent code to improve readability
  • Use one blank line to separate code sections
  • Use ANSI Joins instead of old-style joins
  • Use proper Comments
  • Avoid create and drop object in the procedure
  • Variable Declare on top of code
  • Do not use column numbers in the ORDER BY clause
  • Avoid  GOTO statement
  • When executing an UPDATE or DELETE statement, use the primary key in the WHERE condition, if possible.
  • Use SET NOCOUNT ON at the beginning of your SQL batches
  • Avoid using “*” in query
  • Keep your transactions as short as possible
  • Use Transaction Begin/Commit for insert update
  • Use sp_executesql instead of Exec(@str) for the execution of dynamic SQL
  • Avoid use Recompile option in store procedure
  • Do not use reserved or keywords as object names
  • Transaction make as smaller as possible
  • Use database level paging as much as possible to reduce network traffic
  • If you have too much table use different schema for each module

Follow Variable prefix

No Type Prefix Remark
1 tinyint @ti  
2 smallint @sm  
3 int @in  
4 bigint @bi  
5 numeric @nu  
6 hierarchyid @hi  
7 bit @bl  
8 date @Dt  
9 time @tm  
10 datetimeoffset @do  
11 datetime @Dt  
12 datetime2 @Dt  
13 money @mo  
14 smallmoney @Sm  
15 decimal @De  
16 float @fl  
17 char @Ch  
18 nchar @nc  
19 varchar @Vc  
20 nvarchar @Nv  
21 uniqueidentifier @Ui  
22 text @Tx  
23 ntext @Nt  
24 sql_variant @Sv  
25 varbinary @Vb  
26 image @Im  
27 xml @Xm  

Catch block template


Spread the love

Leave a Comment