CFSTOREDPROC |
Description
Executes a stored procedure in 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
ColdFusion MX: Deprecated the connectString, dbName, dbServer, dbtype, provider and providerDSN attributes. They do not work, and might cause an error, in releases later than ColdFusion 5. (ColdFusion MX uses Type 4 JDBC drivers.)
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. 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>
    --->

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