MACROMEDIA COLFUSION MX 7 - INSTALLING AND USING COLDFUSION MX, Reference

Page: 405 / 1108

cfstoredproc 405
Usage
Use this tag to call a database stored procedure. Within this tag, you code
cfprocresult
and
cfprocparam
tags as follows:
•
cfprocresult
If the stored procedure returns one or more result sets, code one
cfprocresult
tag per result set.
•
cfprocparam
If the stored procedure uses input or output parameters, code one
cfprocparam
tag per parameter, ensuring that you include every parameter in the stored
procedure definition. Additionally, you must code
cfprocparam
tags in the same order as the
parameters in the stored procedure definition.
If you set
returnCode = "Yes"
, this tag sets the variable
prefix. statusCode
, which holds the
status code for a stored procedure. Status code values vary by DBMS. For the meaning of code
values, see your DBMS documentation.
This tag sets the variable
prefix. ExecutionTime
, which contains the execution time of the
stored procedure, in milliseconds.
The value of
prefix
is either
cfstoredproc
or the value specified by the
result
attribute, if it is
set. The
result
attribute provides a way for stored procedures that are called from multiple
pages, possibly at the same time, to avoid overwriting the results of one call with another. If you
set the
result
attribute to
myResult
, for example, you would access
ExecutionTime
as
myResult.ExecutionTime
. Otherwise, you would access it as
cfstoredproc.ExecutionTime.
Before implementing this tag, ensure that you understand stored procedures and their usage.
The following examples use a Sybase stored procedure; for an example of an Oracle 8 or 9 stored
procedure, see
cfprocparam
.
Example
<!--- This view-only example executes a Sybase stored procedure that
returns three result sets, two of which we want. The stored
procedure returns the status code and one output parameter,
which we display. We use named notation for the parameters. --->
<!---
<cfstoredproc procedure = "foo_proc"
dataSource = "MY_SYBASE_TEST" username = "sa"
password = "" dbServer = "scup" dbName = "pubs2"
returnCode = "Yes" debug = "Yes">
<cfprocresult name = RS1>
<cfprocresult name = RS3 resultSet = 3>
returnCode Optional No
•
Yes: populates
cfstoredproc.statusCode
with status
code returned by the stored procedure.
• No
result Optional Specifies a name for the structure in which
cfstoredproc
returns the statusCode and
ExecutionTime variables. If set, this value replaces
cfstoredproc
as the prefix to use when accessing those
variables. For more information, see the Usage section.
Attribute Req/Opt Default Description