[Prev] [Next] [Top] [Bottom] [Contents] (3 out of 9)

Stored Procedures

Stored Procedures are SQL objects that are stored in a database. These objects are maintained by the database itself. Stored Procedures accept arguments that are used as variables throughout the SQL contained in the stored procedure.

Creating Stored Procedures

Stored procedures can be created by using any tools available for the database implementation you are using. They also can be created from within the tool by using the Object Editor.

Vendor-Specific Differences

Each vendor has its own distinct Stored Procedure implementation. This is a description of each vendor's Stored Procedure as it pertains to Sapphire/Web.

Sybase

Oracle

Informix

Note: Since stored procedure implementations vary from vendor to vendor, see the documentation provided by the database vendor for further information on Stored Procedures.

Sybase and Informix Limitations

When creating Sybase and Informix stored procedures, there are some limitations.

Sybase's Transact SQL language and Informix's SPL will allow stored procedures to contain conditional logic. However, because Sapphire/Web requires information about data returned by a Stored Procedure, a stored procedure must be executed with dummy arguments in order to obtain this information. If conditional logic is performed based upon argument values, the information about the data could be incorrect when dummy arguments are used. Therefore, conditional logic must be performed so SELECT statements always contain the same SELECT LISTS, and one of the conditions checks for null and performs a SELECT with the same SELECT LIST.

The following is an example of a Sybase Stored Procedure using conditional logic that can be interpreted by Sapphire/Web:

CREATE PROCEDURE conlogic
	(@which int)
AS
/* notice that each SELECT LIST is the same */
if ( @which = 0 )
	select title, price from titles where price > 9.99
else if ( @which != 0 AND @which != null )
	select title, price from titles where total_sales > 
@which
else if ( @which = null ) /* needed for Sapphire/Web */
	select title, price from titles


[Prev] [Next] [Top] [Bottom] [Contents] (3 out of 9)