CFSTOREDPROC  
Description

Executes a stored procedure by a JDBC connection to a server database. It specifies database connection information and identifies the stored procedure.

 
Category

Database manipulation tags

 
Syntax
    <cfstoredproc 
   procedure = "procedure name"
   dataSource = "ds_name"
   username = "username"
   password = "password"
   blockFactor = "blocksize"
   debug = "Yes" or "No"
   returnCode = "Yes" or "No">

  
 
See also

cfinsert, cfqueryparam, cfprocparam, cfprocresult, cftransaction, cfquery, cfupdate

 
History

New in ColdFusion MX: The connectString, dbName, dbServer, dbtype, provider and providerDSN attributes are deprecated. Do not use them. They do not work, and might cause an error, in releases later than ColdFusion 5. (ColdFusion uses Type 4 JDBC drivers.)

 
Usage

Within this tag, you code cfprocresult and cfprocparam tags as necessary.

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>
--->
PROCEDURE  
  Required
 

Name of stored procedure on database server.

DATASOURCE  
  Required
 

Name of data source that points to database that contains stored procedure.

USERNAME  
  Optional
 

Overrides username in data source setup.

PASSWORD  
  Optional
 

Overrides password in data source setup.

BLOCKFACTOR  
  Optional
 
Default value: "1"

Maximum number of rows to get at a time from server. Range is 1 to 100.

DEBUG  
  Optional
 
Default value: "No"
  • Yes: Lists debug information on each statement
  • No
RETURNCODE  
  Optional
 
Default value: "No"
  • Yes: Tag populates cfstoredproc.statusCode with status code returned by stored procedure.
  • No