cfstoredproc

Last update:

Jun 9, 2026

Note:

This tag is unsupported in CFFiddle.

Description

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

Syntax

<cfstoredproc 

dataSource = "data source name" 

procedure = "procedure name" 

cachedAfter = "date" 

cachedWithin = "time span" 

debug = "yes|no" 

dbPool="connection pool"

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.

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.
cacheID	Optional		ID to be used to store query result in cache. This ID can be used to either retrieve or remove query from cache
cacheRegion	Optional		Cache region to be used to cache query result. If not specified, by default query is cached in the STOREDPROC region.
clientInfo	Optional		Structure containing properties of the client to be set on the database connection.
dbPool	Optional		Specifies the database connection pool that should be used for the stored procedure execution. dbPool can help choose a specific connection pool or load balance databases by distributing queries across multiple pools.
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" in the cfstoredproc tag, ColdFusion populates the statusCode key in the result structure with the stored procedure's return status code. The return status code is separate from any output parameters defined using cfprocparam. 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.}

Example

<!---

Example: Execute a stored procedure that retrieves book details.



Prerequisites:

1. Configure a data source named bookDSN.

2. Create a stored procedure named GetBookById.

3. The procedure accepts bookId as an input parameter,

   returns bookTitle as an output parameter, and returns

   a result set containing book_id, title, and price.



The exact stored procedure implementation and parameter

syntax depend on the database management system.

--->



<cfset bookId = 101>



<cfstoredproc

    procedure="GetBookById"

    datasource="bookDSN"

    result="procInfo"

    returnCode="yes">



    <!--- Input parameter --->

    <cfprocparam

        type="in"

        cfsqltype="cf_sql_integer"

        value="#bookId#">



    <!--- Output parameter --->

    <cfprocparam

        type="out"

        cfsqltype="cf_sql_varchar"

        variable="bookTitle">



    <!--- Result set returned by the procedure --->

    <cfprocresult name="bookResults">



</cfstoredproc>



<!--- Display the output parameter --->

<cfoutput>

    <p>Book title: #bookTitle#</p>

</cfoutput>



<!--- Display the result set --->

<cfoutput query="bookResults">

    <p>

        Book ID: #book_id#<br>

        Title: #title#<br>

        Price: #price#

    </p>

</cfoutput>



<!--- Display procedure execution information --->

<cfoutput>

    <p>Rows returned: #bookResults.recordCount#</p>

    <p>Return code: #procInfo.statusCode#</p>

    <p>Execution time: #procInfo.ExecutionTime# ms</p>

</cfoutput>

Was this page helpful?

We're glad. Tell us how this page helped.

Found the answer to my problem Understood the instructions Liked the feature

Other suggestions

We're sorry. Can you tell us what didn't work for you?

Didn't find the answer to my problem Couldn't understand the instructions Didn't like the feature

Other suggestions

Thank you for your feedback. Your response will help improve this page.

Was this helpful?

We are sorry the content didn't meet your needs.

Share additional feedback to help us improve.

0/255 | Character limit exceeded.

Thank you so much for sharing your feedback!