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:

  • Specifying call parameters in a CFQUERY
  • Using CFSTOREDPROC and its associated tags
 
 
  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:

  • CFPROCPARAM -- Set the data and procedure types, variables and values, and other options
  • CFPROCRESULT -- Identifies the result set for output and optionally limits the result set if multiple sets are returned by the stored procedure

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.