Basic rules for coding in Stored Procedures in SQL Server

Following are few impoortant tips for coding Stored Procedure in SQL Server;

1.Ownership

a)The developer writing the Stored Procedure must provide his / her Full Name,
Create Date, and brief Description of the Stored Procedure. This commented
information should be the first part of the Stored Procedure.

-- Author: Atif Sheikh
-- Create date : 25-Aug-08
-- Description : Brief Description


2.SET Options

a)The Following options should be set in Each Stored Procedure,

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON


3.Naming Conventions

a)Stored Procedure name should start with ‘usp’. The next alphabet after ‘usp’
should be CAPITAL and each recognizable word in the name of Stored Procedure
should start with CAPITAL letter. For example ‘uspInsertProcedure’

b)INPUT parameters of Stored Procedure should start with ‘@p’ and end with ‘_IN’.
For Example, @pInputVariable_IN. The next alphabet after ‘@p’ should be CAPITAL
and each recognizable word in the name of variable should start with CAPITAL
letter.

c)OUTPUT parameters of Stored Procedure should start with ‘@p’ and end
with ‘_OUT’. For Example, @pOutputVariable_OUT. The next alphabet after ‘@p’
should be CAPITAL and each recognizable word in the name of variable should
start with CAPITAL letter.

d)Scope variables should start with ‘@’ sign, following with the initials of the
data type of the respective variable. The next alphabet after ‘@’ + initials of
the data type should be CAPITAL and each recognizable word in the name of
variable should start with CAPITAL letter. For example, @intIntegerVariable,
@varcVarcharVariable, @varbVarbinaryVariable, etc.



4.Data Types

a)The data Types used in the Stored Procedure should be the SQL Server Native data
Types. If any User Defined data type is used in the Stored Procedure, the
comment must be added in the code.

b)The length and precision should be provided in accordance with the usage of the
variable.


5.Return IDENTITY

a)In order to return the IDENTITY value from INSERT Stored Procedures,
SCOPE_IDENTITY should be used.

b)Proper OUTPUT parameter should be defined in the Stored Procedure to return the
value of IDENTITY.

6.Dynamic SQL

a)Dynamic SQL should be avoided as much as possible. But, if there is the need of
the code, execute the SQL string using ‘sp_Executesql’ with proper parameters.

b)In case of DDL Statements, EXECUTE can be used.


7.Code Indenting

a)The Code of the Stored Procedure should be properly Indented and readable. The
spacing can be controlled by using TAB key (equal to 4 spaces).

b)Each Block of code should begin with BEGIN keyword and end with END keyword.


8.TRANSACTION

a)The DML operations should be controlled by TRANSACTIONS. These Transactions
should be synchronized with the Front-End transactions to avoid any conflict.

b)Do not Set TRANSACTION ISOLATION LEVEL without consulting the Database Department


9.TRY / CATCH

a)TRY / CATCH Block should be the part of every Stored Procedure to control and
record errors.


10.Return ERROR_NUMBER

a)Proper Error Number should be returned from the CATCH Block to the application.


11.Nested SP calls

a)Nested Stored Procedure calls should be avoided. In case of necessity, the
TRANSACTION should be applied to the master Stored Procedure, calling the rest
of the Sps.


12.INSERT Statements

a)INSERT Statements should contain the column names.


13.SELECT Statements

a)SELECT Statements should contain the column names.