User Guide

278 Chapter 2: ColdFusion Tags
cfprocresult
Description
Associates a query object with a result set returned by a stored procedure. Other ColdFusion tags,
such as
cfoutput and cftable, use this query object to access the result set. This tag is nested
within a
cfstoredproc tag.
Category
Database manipulation tags
Syntax
<cfprocresult
name = "query_name"
resultSet = "1-n"
maxRows = "maxrows">
See also
cfinsert, cfprocparam, cfquery, cfqueryparam, cfstoredproc, cftransaction,
cfupdate
Attributes
Usage
To enable access to data returned by the stored procedure, specify one or more cfprocresult
tags. If the stored procedure returns more than one result set, use the
resultSet attribute to
specify which of the stored procedure’s result sets to return.
The
resultSet attribute must be unique within the scope of the cfstoredproc tag. If you
specify a result set twice, the second occurrence overwrites the first.CFML supports Oracle 8
Reference Cursor type, which passes a parameter by reference. Parameters that are passed this way
can be allocated and deallocated from memory within the execution of one application. To use
reference cursors in packages or stored procedures, use the
cfprocresult tag. This causes the
ColdFusion JDBC database driver to put Oracle reference cursors into a result set. (You cannot
use this method with Oracle's ThinClient JDBC drivers.)
Example
<!--- This example executes a Sybase stored procedure that returns three
result sets, two of which we want. The stored procedure returns
status code and one output parameter, which we display. We use
named notation for parameters. --->
<!--- cfstoredproc tag --->
<cfstoredproc procedure = "foo_proc"
dataSource = "MY_SYBASE_TEST" username = "sa"
password = "" dbServer = "scup" dbName = "pubs2"
returnCode = "Yes" debug = "Yes">
<!--- cfprocresult tags --->
<cfprocresult name = RS1>
<cfprocresult name = RS3 resultSet = 3>
<!--- cfprocparam tags --->
Attribute Req/Opt Default Description
name Required Name for the query result set.
resultSet Optional 1 Names one result set, if stored procedure returns more than one.
maxRows Optional -1 (All) Maximum number of rows returned in result set.