User Guide

cfstoredproc 329
• cfprocparam If the stored procedure uses input or output parameters, code one
cfprocparam tag per parameter. 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 cfstoredproc.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
cfstoredproc.ExecutionTime, which contains the execution time of
the stored procedure, in milliseconds.
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 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>
<cfprocparam type = "IN" CFSQLType = CF_SQL_INTEGER
value = "1" dbVarName = @param1>
<cfprocparam type = "OUT" CFSQLType = CF_SQL_DATE
variable = FOO dbVarName = @param2>
</cfstoredproc>
--->
<!---
<cfoutput>The output param value: ’#foo#’<br></cfoutput>
<h3>The Results Information</h3>
<cfoutput query = RS1>#name#,#DATE_COL#<br></cfoutput><p>
<cfoutput>
<hr>
<p>Record Count: #RS1.recordCount# >p>Columns: #RS1.columnList#<hr>
</cfoutput>
<cfoutput query = RS3>#col1#,#col2#,#col3#<br>
</cfoutput><p>
<cfoutput>
<hr>
<p>Record Count: #RS3.recordCount# <p>Columns: #RS3.columnList#<hr>
The return code for the stored procedure is: ’#cfstoredproc.statusCode#’<br>
</cfoutput>
--->