User Guide Cancel

cfstoredproc

Last updated on May 30, 2024 | Also applies to ColdFusion

ColdFusion

Open app

CFML Reference User Guide
ColdFusion functions
ColdFusion Tags
1. ColdFusion tag summary
2. ColdFusion tags by category
  1. Application framework tags
    1. cfapplication
    2. cfassociate
    3. cferror
    4. cfimport
    5. cfinterface
    6. cflock
    7. cfscript
    8. cfthread
  2. Communications tags
  3. Database manipulation tags
  4. Data output tags
    1. cfchart
    2. cfchartdata
    3. cfchartseries
    4. cfcol
    5. cfcontent
    6. cfdocument
    7. cfdocumentitem
    8. cfdocumentsection
    9. cfflush
    10. cfheader
    11. cflog
    12. cfoutput
    13. cfpresentation
    14. cfpresentationslide
    15. cfpresenter
    16. cfprocessingdirective
    17. cfprint
    18. cfreport
    19. cfreportparam
    20. cfsilent
    21. cftable
  5. Debugging tags
    1. cfdump
    2. cftimer
    3. cftrace
  6. Exception handling tags
    1. cfcatch
    2. cferror
    3. cffinally
    4. cfrethrow
    5. cfthrow
    6. cftry
  7. Extensibility tags
    1. cfchart
    2. cfchartdata
    3. cfchartseries
    4. cfcollection
    5. cfcomponent
    6. cfexecute
    7. cfftp
    8. function
    9. cfindex
    10. cfinterface
    11. cfinvoke
    12. cfinvokeargument
    13. cfobject
    14. cfproperty
    15. cfreport
    16. cfreportparam
    17. cfreturn
    18. cfsearch
    19. cfsharepoint
    20. cfspreadsheet
    21. cfwddx
    22. cfxml
  8. File management tags
    1. cfdirectory
    2. cffile
    3. cffileupload
    4. cfftp
    5. cfzip
    6. cfzipparam
  9. Flow-control tags
    1. cfabort
    2. cfbreak
    3. cfcase
    4. cfcontinue
    5. cfdefaultcase
    6. cfelse
    7. cfelseif
    8. cfexecute
    9. cfexit
    10. cfif
    11. cfinclude
    12. cflocation
    13. cfloop
    14. cfrethrow
    15. cfswitch
    16. cfthrow
    17. cftry
  10. Forms tags
    1. cfapplet
    2. cfcalendar
    3. cffileupload
    4. cfform
    5. cfformgroup
    6. cfformitem
    7. cfgrid
    8. cfgridcolumn
    9. cfgridrow
    10. cfgridupdate
    11. cfinput
    12. cfpdf
    13. cfpdfform
    14. cfpdfformparam
    15. cfpdfparam
    16. cfpdfsubform
    17. cfselect
    18. cfslider
    19. cftextarea
    20. cftree
    21. cftreeitem
  11. Internet Protocol tags
    1. cfajaximport
    2. cfajaxproxy
    3. cfftp
    4. cffeed
    5. cfimap
    6. cfhttp
    7. cfhttpparam
    8. cfldap
    9. cfmail
    10. cfmailparam
    11. cfmailpart
    12. cfpop
    13. cfsprydataset
  12. Page processing tags
    1. cfcache
    2. cfcontent
    3. cfflush
    4. cfheader
    5. cfhtmlhead
    6. cfinclude
    7. cfprocessingdirective
    8. cfsetting
    9. cfsilent
  13. Security tags
  14. Variable manipulation tags
    1. cfcookie
    2. cfdump
    3. cfparam
    4. cfregistry
    5. cfsavecontent
    6. cfschedule
    7. cfset
    8. cfsetting
  15. Other tags
    1. cfimage
    2. cflog
    3. cfregistry
3. Tags a-b
4. Tags c
  1. cfcache
  2. cfcalendar
  3. cfcase
  4. cfcatch
  5. cfchart
  6. cfchartdata
  7. cfchartseries
  8. cfclient
  9. cfclientsettings
  10. cfcol
  11. cfcollection
  12. cfcomponent
  13. cfcontent
  14. cfcontinue
  15. cfcookie
  16. Tags d-e
5. Tags f
6. Tags g-h
  1. cfgraph
  2. cfgraphdata
  3. cfgrid
  4. cfgridcolumn
  5. cfgridrow
  6. cfgridupdate
  7. cfheader
  8. cfhtmlhead
  9. cfhtmltopdf
  10. cfhtmltopdfitem
  11. cfhttp
  12. cfhttpparam
7. Tags i
  1. cfif
  2. cfimage
  3. cfimap
  4. cfimapfilter
  5. cfimpersonate
  6. cfimport
  7. cfinclude
  8. cfindex
  9. cfinput
  10. cfinsert
  11. cfinterface
  12. cfinvoke
  13. cfinvokeargument
8. Tags j-l
9. Tags m-o
10. Tags p-q
  1. cfparam
  2. cfpdf
  3. cfpdfform
  4. cfpdfformparam
  5. cfpdfparam
  6. cfpdfsubform
  7. cfpod
  8. cfpop
  9. cfpresentation
  10. cfpresentationslide
  11. cfpresenter
  12. cfprint
  13. cfprocessingdirective
  14. cfprocparam
  15. cfprocresult
  16. cfprogressbar
  17. cfproperty
  18. cfquery
  19. cfqueryparam
11. Tags r-s
  1. cfregistry
  2. cfreport
  3. cfreportparam
  4. cfrethrow
  5. cfreturn
  6. cfsavecontent
  7. cfschedule
  8. cfscript
  9. cfsearch
  10. cfselect
  11. cfservlet
  12. cfservletparam
  13. cfset
  14. cfsetting
  15. cfsharepoint
  16. cfsilent
  17. cfslider
  18. cfspreadsheet
  19. cfsprydataset
  20. cfstoredproc
  21. cfswitch
12. Tags t
  1. cftable
  2. cftextarea
  3. cftextinput
  4. cfthread
  5. cfthrow
  6. cftimer
  7. cftooltip
  8. cftrace
  9. cftransaction
  10. cftree
  11. cftreeitem
  12. cftry
13. Tags u-z
  1. cfupdate
  2. cfwddx
  3. cfwebsocket
  4. cfwindow
  5. cfxml
  6. cfzip
  7. cfzipparam
CFML Reference
1. Reserved words and variables
2. Ajax JavaScript functions
3. ColdFusion ActionScript functions
4. ColdFusion mobile functions
5. Application.cfc reference
6. Script functions implemented as CFCs
7. ColdFusion Flash Form style reference
8. ColdFusion event gateway reference
9. ColdFusion C++ CFX Reference
10. ColdFusion Java CFX reference
11. WDDX JavaScript Objects
Cloud services

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

Adobe

Get help faster and easier

New user?

cfstoredproc

Description

Category