BackUp LevelNext

Executing Stored Procedures

Many database systems allow you to create stored procedures to return result sets for commonly-used data queries. Check your DBMS documentation for details of your system's implementation of this feature.

This section describes two methods of coding access to stored procedures:

Calling stored procedures from CFQUERY

You can call stored procedures from within CFQUERY, as in the example below. This query calls a SQL Server stored procedure that retrieves all orders due to ship on the date specified:

<CFQUERY NAME="GetOrdersForDate"
    DATASOURCE="Orders Database">
        { call OrderDB.dbo.sp_getorders( #OrderDate# ) }
</CFQUERY>

While this method is still available, we recommend using the CFSTOREDPROC tag to add greater flexibility and control to stored procedure operations.

Calling stored procedures from CFSTOREDPROC

The CFSTOREDPROC tag wraps SQL call parameters in tag attributes to simplify the process of retrieving data and to add functions not available in a CFQUERY call statement. It supports both ODBC and native data sources.

CFSTOREDPROC is used to identify the stored procedure and its data source and to set options. Two additional tags are nested within it:

Before deciding which of the two stored procedure methods to employ, answer these questions:

Do I need to specify input/output parameters for the query?

Do I need to return a result code for the query?

Do I need to select from multiple result sets in a stored procedure?

If you answer Yes to all three of these questions, you should be using CFSTOREDPROC in your applications.


BackUp LevelNext

allaire

AllaireDoc@allaire.com
Copyright © 1998, Allaire Corporation. All rights reserved.