
Contents > Developing ColdFusion MX Applications > Designing and Optimizing a ColdFusion Application > Optimizing ColdFusion applications > Optimizing database use Using stored procedures

Using stored procedures

The cfstoredproc tag lets ColdFusion MX use stored procedures in your database management system. A stored procedure is a sequence of SQL statements that is assigned a name, compiled, and stored in the database system. Stored procedures can encapsulate programming logic in SQL statements, and database systems are optimized to execute stored procedures efficiently. As a result, stored procedures are faster than cfquery tags.

You use the cfprocparam tag to send parameters to the stored procedure, and the cfproresult tag to get the record sets that the stored procedure returns.

The following example executes a Sybase stored procedure that returns three result sets, two of which the example uses. The stored procedure returns the status code and one output parameter, which the example displays.

<!--- cfstoredproc tag --->
<cfstoredproc procedure = "foo_proc" dataSource = "MY_SYBASE_TEST" 
   username = "sa" password = "" returnCode = "Yes">

   <!--- cfprocresult tags --->
   <cfprocresult name = RS1>
   <cfprocresult name = RS3 resultSet = 3>

   <!--- cfprocparam tags --->
   <cfprocparam type = "IN"
      CFSQLType = CF_SQL_INTEGER
         value = "1"    dbVarName = @param1>
   <cfprocparam type = "OUT"   CFSQLType = CF_SQL_DATE
      variable = FOO dbVarName = @param2>
<!--- Close the cfstoredproc tag --->
</cfstoredproc>

<cfoutput>
   The output param value: '#foo#'<br>
</cfoutput>

<h3>The Results Information</h3>
<cfoutput query = RS1>
   #name#,#DATE_COL#<br>
</cfoutput>
<br>
<cfoutput>
   <hr>
   Record Count: #RS1.recordCount#<br>
   Columns: #RS1.columnList#<br>
   <hr>
</cfoutput> 

<cfoutput query = RS3>
   #col1#,#col2#,#col3#<br>
</cfoutput>
<br>
<cfoutput>
   <hr><br>
   Record Count: #RS3.recordCount#<br>
   Columns: #RS3.columnList#<br>
   <hr>

   The return code for the stored procedure is: '#cfstoredproc.statusCode#'<br>
</cfoutput>

Reviewing the code

The following table describes the code and its function:

Code	Description
<cfstoredproc procedure = "foo_proc" dataSource = "MY_SYBASE_TEST" username = "sa" password = "" returnCode = "Yes">	Runs the stored procedure foo_proc on the MY_SYBASE_TEST data source. Populates the `cfstoredproc` `statusCode` variable with the status code returned by stored procedure.
<cfprocresult name = RS1> <cfprocresult name = RS3 resultSet = 3>	Gets two record sets from the stored procedure: the first and third result sets it returns.
<cfprocparam type = "IN" CFSQLType = CF_SQL_INTEGER value = "1" dbVarName = @param1> <cfprocparam type = "OUT" CFSQLType = CF_SQL_DATE variable = FOO dbVarName = @param2> </cfstoredproc>	Specifies two parameters for the stored procedure, an input parameter and an output parameter. Sets the input parameter to 1 and the ColdFusion variable that gets the output to FOO. Ends the `cfstoredproc` tag body.
<cfoutput> The output param value: '#foo#'<br> </cfoutput> <h3>The Results Information</h3> <cfoutput query = RS1> #name#,#DATE_COL#<br> </cfoutput> <br> <cfoutput> <hr> Record Count: #RS1.recordCount#<br> Columns: #RS1.columnList#<br> <hr> </cfoutput> <cfoutput query = RS3> #col1#,#col2#,#col3#<br> </cfoutput> <br> <cfoutput> <hr><br> Record Count: #RS3.recordCount#<br> Columns: #RS3.columnList#<br> <hr> The return code for the stored procedure is: '#cfstoredproc.statusCode#'<br> </cfoutput>	Displays the results of running the stored procedure: The output parameter value, The contents of the two columns in the first record set identified by the name and DATE_COL variables. You set the values of these variables elsewhere on the page. The number of rows and the names of the columns in the first record set The contents of the columns in the other record set identified by the col1, col2, and col3 variables. The number of rows and the names of the columns in the record set The status value returned by the stored procedure.

For more information on creating stored procedures, see your database management software documentation. For more information on using the cfstoredproc tag, see CFML Reference.


Contents > Developing ColdFusion MX Applications > Designing and Optimizing a ColdFusion Application > Optimizing ColdFusion applications > Optimizing database use Using stored procedures

Version 6.1

Comments are no longer accepted for ColdFusion MX 6.1. ColdFusion 8 is the current version.

Send me an e-mail when comments are added to this page | Comment Report

Current page: http://livedocs.adobe.com/coldfusion/6.1/htmldocs/appfra28.htm