General Guidelines
•Each Procedure should have header information
»File Name
»Version
»Procedure Name
»Purpose
»Author
»Date
»Purpose
•Code Indentation
should be maintained properly (Use tab space for indentation).
e.g.,
BEGIN
SELECT name into name_tmp
FROM emp
WHERE empcode =
empcode_tmp
AND sal
= 10000
END
•Use simple logic
in procedures
•When a complex
logic is used, explain the logic with proper comments or pseudo code.
•When modifications are done in the procedure,
modification details should be added. Add these details just above the code
being modified
•/* Modified by:
<Name of the person> */
•/* Modified
Date: < Date of modification > */
•/* Bug No:
<No of the bug for which the modification is done> */
•/* Bug
Description: <Short description of the bug> */
•Comments should
specify why the coding is done. Avoid
using &, @, “;” “:”, and full stop at beginning of a new line in comments.
nLength of the database object names, variable names should not
exceed 25 characters.
–Table Name, Stored Procedure
name, View name, User defined data type name etc.
nDeclaration of all the local variables should
be done at the beginning of the procedure.
nAlways use Standard UDD’s for declaring local
variables
nProcedure/ Function parameter’s UDD should
match with that of the method parameters.
nUDD of the variables declared within the
stored procedures/ functions should match with that of the underlying columns
of the table.
nNames of local variables should be meaningful as per the
context.
nReserved words like ROLE, DATE, DELETE should not be used for
object names and variable names.
nvariables should not start with a number or a special character
like @2position
nLTRIM, RTRIM should be done for all parameters
and variables
nWhere necessary convert a character data to UPPER case and
store in a separate procedure
nLTRIM, RTRIM, UPPER should not be used as part of the WHERE
clause of a query
nLTRIM / RTRIM / UPPER should not be used for date / integer
fields
nLTRIM / RTRIM /
UPPER should not be used against a table column in a where clause of a SELECT
query
nConvert the
table column values to UPPER or TRIM the value before storing the same in the
table – before INSERT
CREATE PROCEDURE proc1
@param1 UDD_EMPLOYEE_NAME,
@param2 UDD_DEPT_NAME
AS
BEGIN
SELECT emp_no, emp_name, dept_name
FROM employee e,
department d
WHERE e.dept = d.dept
AND e.employee_name =
UPPER(@param1)
AND d.dept_name =
UPPER(@param2)
/* Above two lines are
not the recommended way of using functions */
/* The same holds good
for RTRIM, LTRIM etc. */
/* Optimizer may not use
the index */
END
-------------------------------------------------------------------------------------------------
CREATE PROCEDURE proc1
@param1 UDD_EMPLOYEE_NAME,
@param2 UDD_DEPT_NAME
AS
BEGIN
SELECT @param1 =
UPPER(@param1)
SELECT @param2 =
UPPER(@param2)
SELECT emp_no, emp_name, dept_name
FROM employee e,
department d
WHERE e.dept = d.dept
AND e.employee_name = @param1
AND d.dept_name = @param2
/* Here the parameters
are already converted into UPPER and stored */
END
-------------------------------------------------------------------------------------------
CREATE PROCEDURE proc1
@param1 UDD_EMPLOYEE_NAME,
@param2 UDD_DEPT_NAME
AS
BEGIN
SELECT @param1 =
UPPER(@param1)
SELECT @param2 =
UPPER(@param2)
SELECT emp_no, emp_name, dept_name
FROM employee e,
department d
WHERE e.dept = d.dept
AND UPPER(e.employee_name) = @param1
AND UPPER(d.dept_name) = @param2
/* Do not use UPPER
against table column */
/* INSERT / UPDATE
statement that saved data should have converted the values to UPPER and saved
*/
END
-----------------------------------------------------------------------------------------
nUse Is Null and Is Not Null operators to check the null
conditions.
e.g.,
Incorrect
SELECT ecode, ename
FROM emp
where
name = null
Correct
SELECT ecode, ename
FROM emp
where
name Is Null
----------------------------------------------------------------------------------------
nDo not use SET operator for
assigning local variables, use SELECT instead.
Not Recommended
SET @transaction_no_tmp = 1
Recommended
SELECT
@transaction_no_tmp = 1
------------------------------------------------------------------------------------------------
nString literals in single
quotes should be in upper case.
nAll database
object names, variable names should be in lower case letters.
nAll Keywords,
reserve words should be in upper case letters
nAll DML
statements should be in upper case
–SELECT, INSERT, UPDATE, DELETE, WHERE, ORDER
BY, GROUP BY, UPPER, LTRIM, RTRIM
--------------------------------------------------------------------------------------------------
nUse parenthesis for complex
arithmetic and logical expressions or statements
nHash (#) temp tables should
not be used. Creation
of # tables increases the disk I/O. Instead We can use Views, Table variable,
Derived tables and advanced SQL.
nSELECT INTO statement should
not be used inside the procedure. This is a DDL statement. DBA would set the
database permission not to allow SELECT INTO
nInstead use a permanent
temporary table and Insert the records into the temporary table
-----------------------------------------------------------------------------------------------------------
nFor temporary
tables, there should be a column called GUID. GUID column should be a NOT NULL
column in the table.
n
nWhen permanent temporary
tables are used, then delete should be done for the respective GUID before and
after the processing.
nIn all stored
procedures referred by report, dcube, view screens
should have NOLOCK.
-----------------------------------------------------------------------------------------------------------
nAll SELECT statement should have NOLOCK table
hint. (Wherever dirty reads does not effect the transaction).
e.g.,
Not Recommended
SELECT cust_name, cust_add, cust_phone_no
FROM customer a, address b
WHERE a.cust_id = b.cust_id
Recommended
SELECT cust_name, cust_add, cust_phone_no
FROM customer a (nolock), address b (nolock)
WHERE a.cust_id = b.cust_id
nAvoid joining more than 5 tables in a single select statement.
----------------------------------------------------------------------------------------------------------------
nDatabase name, server name,
user name should not be hard coded in the procedures.
nDo not return values from
stored procedures like functions.
nUse output variables instead
to return values to the caller.
nOutput variables
to be checked in all the calling procedures
nRAISERROR should
be followed by a return statement
nDo not compare fields of
different data types
nAvoid while
loops if the same can be achieved using table joins.
nUse SET NOCOUNT
ON at the beginning of SQL batches,
stored procedures and triggers
nThis suppresses
messages like '(1 row(s) affected)' after executing INSERT, UPDATE, DELETE and
SELECT statements.
nThis improves
the performance of stored procedures by reducing network traffic. This is also
a MUST in RVW Coding
------------------------------------------------------------------------------------------------------------------
nTry Avoiding functions in where clause / Select Statements.
e.g.,
Not Recommended
SELECT ename, eage, esal
FROM employee
WHERE LTRIM(ecode) = LTRIM(@param1)
Recommended
SELECT @param1 =
LTRIM(@param1)
SELECT ename, eage, esal
FROM
employee
WHERE ecode = @param1
nE.g. comparing a date field in table with GETDATE() can be done
after assigning the GETDATE() value to a temporary variable and using it in the
query
Not Recommended
SELECT ename, eage, esal
FROM
employee
WHERE
joining_date < getdate()
Recommended
SELECT @date_tmp = getdate()
SELECT ename, eage, esal
FROM
employee
WHERE
joining_date < @date_tmp
nThis is to be avoided as the function is executed for each and
every row selected / executed in the WHERE / SELECT.
nEnsure that all the major keys will be part of the WHERE
clause.
nWHERE clause should contain the column names in order of the
index created on the table.
e.g., Index of Employee table is empid, deptid
Not Recommended
SELECT
empid,ename,esal
FROM employee
WHERE depid =
‘D01’
AND empid =
‘E0010’
Recommended
SELECT
empid,ename,esal
FROM employee
WHERE empid =
‘E0010’
AND
depid = ‘D01’
nWhere clause of a SELECT
statement should include the index column from major key onwards
e.g.,
Index on Employee_family table is empid,
Srl_no,
family_member_name
Not Recommended –
Index wont be used by query optimizer
SELECT empid,ename,
family_member_name
FROM Employee_family
WHERE srl_no = 1
AND family_member_name
= ‘Sudha’
Recommended
SELECT empid,ename
, family_member_name
FROM Employee_family
WHERE empid =
‘E0010’
AND srl_no = 1
AND family_member_name
= ‘Sudha’
------------------------------------------------------------------------------------
nNegative logic (Eg : !=, <>, not in) always results in a
table/index scan unless the query is covered by the index covering.
–Not Recommended
SELECT empname, deptid
FROM employee
WHERE deptid NOT IN (‘HR’, ‘ADMIN’)
–Recommended
SELECT empname, deptid
FROM employee
WHERE deptid IN (‘EDP’, ‘ACCTS’)
nReplace inequality operator in the search argument with
alternate operators.
EX :
(1) SELECT *
FROM orders
WHERE price != 0
instead use
SELECT *
FROM orders
WHERE price > 0
(2) SELECT
* FROM orders
WHERE price * 2 < 500
instead use
SELECT *
FROM orders
WHERE
price < 250
nUse joins wherever possible, instead of using sub-queries.
Not Recommended
SELECT cust_name, cust_total_purchase
FROM customer
WHERE cust_id in ( SELECT cust_id
FROM customer_sales
)
Recommended
SELECT cust_name, cust_total_purchase
FROM customer a, customer_sales b
WHERE a.cust_id = b.cust_id
