NCSU ACS Guidelines for Stored Procedures

The following guidelines provide prerequisites for the acceptance of any stored procedure into the NCSU ACS production environment as of 1 May 1997. Stored procedures submitted for use in the ACS production environment will be reviewed for code efficiency and conformance to these guidelines:

To be accepted into the ACS production environment, all stored procedures must:

• be implemented efficiently to minimize impact upon the dataservers.
• use transaction processing where appropriate to address consistency and recovery issues.
• provide a valid return code upon termination.
• provide error-checking for all significant SQL commands and calls to other stored procedures.
• handle all errors appropriately (i.e. if a procedure is dependent upon a data table and fails, it may not be prudent to subsequently truncate that data table)
• where appropriate, issue progress output indicating the invocation and completion of the procedure, as well as general functional progress and error reporting.
• provide thorough documentation in the procedure header as well as throughout the procedure code
• use consistent coding styles which properly reflect logical interpretation and which is conducive to readability -- making appropriate use of indentation, line spacing, and mixed character case.
• be enveloped by the ACS Systems maintenance wrapper statements.

Examples of stored procedures which conform to these guidelines...

Transaction Processing

Transaction processing is a SQL Server mechanism which ensures data consistency within a specified block of code and automatic data recovery when desired. When using transaction processing within a stored procedure, several SQL statements are treated as if a single operation, preventing other processes from accessing data that may be undergoing change between transaction statements. Transaction processing also allows automatic restoration of data when desired within the transaction; for example, when an error is detected.

A good candidate for transaction processing would be a procedure which needs to modify multiple tables simultaneously without possible interference from other processes. Any procedure which needs its resources protected from outside alteration during processing or requires data recovery in the event of an error should consider using transactions. For more information on transaction processing, its use and limitations, refer to the online Sybase documentation available here at ACS or the Sybase SyBooks WWW Server.

Example of an ACS production stored procedures which uses transaction processing...

Progress Output

Procedures which would benefit from logging (or tracing) features should issue messages which indicate their invocation and completion, as well as general functional progress and error reporting. Even if such procedures are infrequently executed or not regularly monitored, the availability of logging information on demand significantly expedites problem determination. Refer to the example code provided for appropriate logging techniques.

Caveats:

• Procedures which are called interactively by PowerBuilder applications should not employ progress reporting. Unfortunately, PowerBuilder interprets printed messages from the SQLserver as errors; therefore, such procedures should restrict printed messages to error reporting only.

• Procedures which are called via the SQLftp server should not employ progress reporting. Such printed messages are not properly handled by certain FTP clients.

Procedure Header

The procedure header will provide information identifying the procedure, its purpose, requirements, behavior, and revision history. The following elements are required:

Procedure Name

Specifies the procedure's name as defined by its CREATE statement.

Version

Number which reflects the current revision of this procedure.

Input

Any required or optional input parameters or data expected by this procedure.

Output

Any changed parameters or data output generated by this procedure.

Return Values

A listing of the return codes generated or passed by this procedure. A valid return code of 0 (zero) must always indicate success.

Dependencies

A listing of any tables or other procedures prerequisite to this specific procedure.

Implementation

A plain english description of the steps this procedure follows to complete its function.

Revision History

A listing of all released revisions of this procedure, each specifying the revision number, date released, programmer name, and a brief description of the changes made.

Click here for a header template.

ACS Systems maintenance wrapper

These instructions will envelope the stored procedure code, preceding its create statement and following its final return execution statement. Using these wrappers will allow us to store local copies all stored procedures and automatically recompile them on a regular basis (this is required to free Sybase system resources which are consumed with each execution of the stored procedure and accumulate over time):

/* ACS Systems maintenance wrapper BEGIN */
use DatabaseName
go
IF EXISTS (SELECT * FROM sysobjects
    WHERE name = 'ProcedureName'
    AND   uid = user_id('dbo')
    AND   type = 'P')
BEGIN
    DROP PROCEDURE ProcedureName
END
go
/* ACS Systems maintenance wrapper END */

/* ACS Systems maintenance wrapper BEGIN */
go
/* ACS Systems maintenance wrapper END */