CFSTOREDPROC

The CFSTOREDPROC tag is the main tag used for executing stored procedures via an ODBC or native connection to a server database. It specifies database connection information and identifies the stored procedure.

Syntax

<CFSTOREDPROC PROCEDURE="procedure name"
    DATASOURCE="ds_name"
    USERNAME="username"
    PASSWORD="password"
    DBSERVER="dbms"
    DBNAME="database name"
    BLOCKFACTOR="blocksize"
    PROVIDER="COMProvider" 
    PROVIDERDSN="datasource" 
    DEBUG="Yes/No"
    RETURNCODE="Yes/No">

PROCEDURE

Required. Specifies the name of the stored procedure on the database server.

DATASOURCE

Required. The name of an ODBC or native data source that points to the database containing the stored procedure.

USERNAME

Optional. If specified, USERNAME overrides the username value specified in the data source setup.

PASSWORD

Optional. If specified, PASSWORD overrides the password value specified in the data source setup.

DBSERVER

Optional. For native database drivers, specifies the name of the database server machine. If specified, DBSERVER overrides the server specified in the data source.

DBNAME

Optional. The database name (Sybase System 11 driver only). If specified, DBNAME overrides the default database specified in the data source.

BLOCKFACTOR

Optional. Specifies the maximum number of rows to fetch at a time from the server. The range is 1 (default) to 100. The ODBC driver may dynamically reduce the block factor at runtime.

PROVIDER

Optional. COM provider (OLE-DB only).

PROVIDERDSN

Optional. Data source name for the COM provider (OLE-DB only).

DEBUG

Optional. Yes or No. Specifies whether debug info will be listed on each statement. Default is No.

RETURNCODE

Optional. Yes or No. Specifies whether the tag populates CFSTOREDPROC.STATUSCODE with the status code returned by the stored procedure. Default is No.

Usage

Within a CFSTOREDPROC tag, you code CFPROCRESULT and CFPROCPARAM tags as necessary.

If you set the ReturnCode parameter to "YES", CFSTOREDPROC sets a variable called CFSTOREDPROC.STATUSCODE, which indicates the status code for the stored procedure. Stored procedure status code values vary by DBMS. Refer to your DBMS-specific documentation for the meaning of individual status code values.

In addition to returning a status code, CFSTOREDPROC sets a variable called CFSTOREDPROC.ExecutionTime. This variable contains the number of milliseconds that it took the stored procedure to execute.

Stored procedures represent an advanced feature, found in high-end database management systems. You should be familiar with stored procedures and their usage before implementing these tags.

Example

...
<!--- The following 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 tag --->
<CFSTOREDPROC PROCEDURE="foo_proc"
    DATASOURCE="MY_SYBASE_TEST"    USERNAME="sa"
    PASSWORD=""    DBSERVER="scup"    DBNAME="pubs2"
    RETURNCODE="YES"    DEBUG>
<!--- CFPROCRESULT tags --->
<CFPROCRESULT NAME = RS1>
<CFPROCRESULT NAME = RS3 RESULTSET = 3>
<!---  CFPROCPARAM tags --->
<CFPROCPARAM TYPE="IN"
    CFSQLTYPE=CF_SQL_INTEGER
        VALUE="1"    DBVARNAME=@param1>
        
<CFPROCPARAM TYPE="OUT"    CFSQLTYPE=CF_SQL_DATE
    VARIABLE=FOO DBVARNAME=@param2>
<!--- Close the CFSTOREDPROC tag --->
</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>
...