Description

Executes a stored procedure in a server database. It specifies database connection information and identifies the stored procedure.

Category

Syntax

<cfstoredproc 
dataSource = "data source name" 
procedure = "procedure name" 
cachedAfter = "date" 
cachedWithin = "time span" 
debug = "yes|no" 
timeOut = "timeout interval" 
fetchClientInfo = "yes|no" 
blockFactor = "block size" 
password = "password" 
result = "result name" 
returnCode = "yes|no" 
username = "user name">

Note:

You can specify this tag's attributes in an attributeCollection attribute whose value is a structure. Specify the structure name in the attributeCollection attribute and use the tag's attribute names as structure keys.

See also

cfinsertcfqueryparamcfprocparamcfprocresultcftransactioncfquerycfupdateOptimizing database use in the Developing ColdFusion Applications

History

ColdFusion 11: Removed the attributes - connectString, dbName, dbServer, dbtype, provider, and providerDSN.

ColdFusion 10: Added the following attributes: timeOut, fetchClientInfo,and clientInfo.

ColdFusion MX 7: Added the result attribute.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. (Releases starting with ColdFusion MX use Type 4 JDBC drivers.)

Attributes

Attribute

Req/Opt

Default

Description

dataSource

Required

 

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

procedure

Required

 

Name of stored procedure on database server.

blockFactor

Optional

1

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

cachedAfter

Optional

 

A date value (for example, April 16, 2008, 4-16-2008). If the date of original query is after this date, ColdFusion uses cached query data. To use cached data, the current query must use same SQL statement, data source, query name, user name, and password. A date/time object is in the range 100 AD-9999 AD.When specifying a date value as a string, enclose it in quotation marks.

cachedWithin

Optional

 

A time span, created using the CreateTimeSpan function. If the original query date falls within the time span, cached query data is used. CreateTimeSpan defines a period from the present, back. Takes effect only if query caching is enabled in the Administrator. To use cached data, the current query must use the same SQL statement, data source, query name, user name, and password.

clientInfo

Optional

 

Structure containing properties of the client to be set on the database connection.

debug

Optional

no

  • yes: lists debug information on each statement.
  • no

fetchClientInfo

Optional

no

If set to yes, returns a struct with the key-value pair passed by the last query.

password

Optional

 

Overrides password in data source setup.

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 Usage.

returnCode

Optional

no

  • yes: populates cfstoredproc.statusCode with status code returned by the stored procedure.
  • no
timeOut Optional

Number of seconds each action is permitted to execute, before returning an error.
The cumulative time may exceed this value. For JDBC statements, ColdFusion sets this attribute. For other drivers, see the driver documentation.

 

username

Optional

 

Overrides username in data source setup.

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.
    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

<cfset ds = "sqltst"> 

<!--- 
If submitting a new book, 
insert the record and display 
confirmation ---> 
<cfif isDefined("form.title")> 

<cfstoredproc procedure="Insert_Book" datasource="#ds#"> 

<cfprocparam 
cfsqltype="cf_sql_varchar" 
value="#form.title#"> 

<cfprocparam 
cfsqltype="cf_sql_numeric" 
value="#form.price#"> 

<cfprocparam 
cfsqltype="cf_sql_date" 
value="#form.publishDate#"> 

<cfprocparam 
cfsqltype="cf_sql_numeric" 
type="out" 
variable="bookId"> 

</cfstoredproc> 

<cfoutput> 
<h3>'#form.title#' inserted into database.The ID is #bookId#.</h3> 
</cfoutput> 

</cfif> 

<cfform action="#CGI.SCRIPT_NAME#" method="post"> 
<h3>Insert a new book</h3> 

Title: 
<cfinput type="text" size="20" required="yes" name="title"/> 
<br/> 

Price: 
<cfinput type="text" size="20" required="yes" name="price" validate="float" /> 
<br/> 

Publish Date: 
<cfinput type="text" size="5" required="yes" name="publishDate" validate="date" /> 
<br/> 

<input type="submit" value="Insert Book"/> 

</cfform> 

<!--- 
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. 
---> 

<!--- 
<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> 
--->

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy