Page 215 - DCAP305_PRINCIPLES_OF_SOFTWARE_ENGINEERING

Unit 10: Coding Standards

• Be specific when declaring objects, such as ADODB. Recordset instead of just Recordset, Notes
to avoid the risk of name collisions.
• Require the use Option Explicit in Visual Basic and VBScript to encourage forethought in
the use of variables and to minimize errors resulting from typographical errors.

• Avoid the use of variables with application scope.
• Use RETURN statements in stored procedures to help the calling program know whether
the procedure worked properly.
• Use early binding techniques whenever possible.
• Use Select Case or Switch statements in lieu of repetitive checking of a common variable
using If…Then statements.
• Explicitly release objects references.
Data-Specific

• Never use SELECT *. Always be explicit in which columns to retrieve and retrieve only
the columns that are required.

• Refer to fields implicitly; do not reference fields by their ordinal placement in a Recordset.
• Use stored procedures in lieu of SQL statements in source code to leverage the performance
gains they provide.
• Use a stored procedure with output parameters instead of single-record SELECT statements
when retrieving one row of data.

• Verify the row count when performing DELETE operations.
• Perform data validation at the client during data entry. Doing so avoids unnecessary
round trips to the database with invalid data.
• Avoid using functions in WHERE clauses.
• If possible, specify the primary key in the WHERE clause when updating a single row.

• When using LIKE, do not begin the string with a wildcard character because SQL Server
will not be able to use indexes to search for matching values.

• Use WITH RECOMPILE in CREATE PROC when a wide variety of arguments are passed,
because the plan stored for the procedure might not be optimal for a given set of parameters.

• Stored procedure execution is faster when you pass parameters by position (the order in
which the parameters are declared in the stored procedure) rather than by name.
• Use triggers only for data integrity enforcement and business rule processing and not to
return information.
• After each data modification statement inside a transaction, check for an error by testing
the global variable @@ERROR.
• Use forward-only/read-only recordsets. To update data, use SQL INSERT and UPDATE
statements.
• Never hold locks pending user input.
• Use uncorrelated subqueries instead of correlated subqueries. Uncorrelated subqueries are
those where the inner SELECT statement does not rely on the outer SELECT statement
for information. In uncorrelated subqueries, the inner query is run once instead of being
run for each row returned by the outer query.

LOVELY PROFESSIONAL UNIVERSITY 209