Basic Rules for coding in User Defined Functions

1.Ownership

a)The developer writing the Function must provide his / her Full Name, Create
Date, and brief Description of the Function. 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)Function name should start with ‘fn. The next alphabet after ‘fn’ should be
CAPITAL and each recognizable word in the name of Stored Procedure should start
with CAPITAL letter. For example ‘fnMyFunction’

b)Parameters of Function should start with ‘@p’. For Example, @pFunctionVariable.
The next alphabet after ‘@p’ should be CAPITAL and each recognizable word in the
name of variable should start with CAPITAL letter.

c)Return variable of Function should start with ‘@pRet’. For Example,
@pRetVariable_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 Function should be the SQL Server Native data Types.
If any User Defined data type is used in the Function, 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.Code Indenting

a)The Code of the Function 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.


6.SELECT Statements

a)SELECT Statements should contain the column names.


7.Table Or Scalar

a)Select right type of function depending upon the return values. If it is a
single value, go for Scalar. If it is a table, then go for table valued function.


7.Performance

a)Always use function in the outer most query if your query is nested.. In this
way you will be applying
function on the final result set. Using it in the inner queries will definitely
effect performance badly. For Example;

-- Wrong approach
-- This query is applying function on all records in employee.
Select * from
(
Select EmployeeID,Name,
fnGetMinimumSalary(tblDesignation.SalaryID) as MinimumSalary
from tblEmployee
Left Outer Join tblDesignation on tblDesignation.ID = tblEmployee.DesignationID
) Main
Inner Join tblEmployeeDetail on tblEmployeeDetail.EmployeeID = Main.EmployeeID
Where tblEmployeeDetail.CountryCode = 'PK'

-- Right approach
-- This query is applying Function only on the Employee with Country Code 'PK'
Select *,fnGetMinimumSalary(tblDesignation.SalaryID) as MinimumSalary
from
(
Select EmployeeID,Name,
SalaryID
from tblEmployee
Left Outer Join tblDesignation on tblDesignation.ID = tblEmployee.DesignationID
) Main
Inner Join tblEmployeeDetail on tblEmployeeDetail.EmployeeID = Main.EmployeeID
Where tblEmployeeDetail.CountryCode = 'PK'