User Guide Cancel

storedproc

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:

In ColdFusion (2018 release), script functions implemented as CFCs are deprecated in ColdFusion.

Description

Used to execute a stored procedure in a server database using CFScript. It specifies database connection information and identifies the stored procedure.

Syntax

Mode	Syntax
Creating the service	new storedProc() or createObject("component", "storedproc")
Initializing the attributes	Any one of the following: storedProcService=new storedproc(attribute-value_pair) storedprocService.setAttributes(attribute-value_pair) storedProcService.set_AttributeName_(attribute_value) storedProcService.execute(attribute-value_pair)
Executing the service action	storedProcService.execute(_attribute-value_pair_)

Mode

Syntax

Creating the service

new storedProc() or createObject("component", "storedproc")

Initializing the attributes

Any one of the following:

storedProcService=new storedproc(attribute-value_pair)
storedprocService.setAttributes(attribute-value_pair)
storedProcService.set_AttributeName_(attribute_value)
storedProcService.execute(attribute-value_pair)

Executing the service action

storedProcService.execute(_attribute-value_pair_)

Properties

datasource	procedure	debug	cachedafter
cachedwithin	blockfactor	password	result
returncode	username

All attributes supported by the tag cfstoredproc are supported as attribute-value pairs. For example,

can be used as

spService.setProcedure("sp_proc");

For details of the cfstoredproc tag attributes, see the Attributes section for cfstoredproc__.

History

ColdFusion 9: Added this function.

Methods

addParam

Description	Used to add cfprocparam tags.
Syntax	storedprocService.addParam(attribute-value pair)
Returns	Nothing
Arguments	All attributes supported by cfprocparam tag can be used as attribute-value pairs.

addProcResult

Description	Used to add cfprocresult tags to associate a query object with a result set returned by a stored procedure.
Syntax	storedprocService.addProcResult(attribute-value pair)
Returns	Nothing
Arguments	All attributes supported by the cfprocresult tag can be used as attribute-value pairs.

execute

Description

Used to execute a stored procedure.

Returns

A component on which the following methods can be invoked:
getProcResultSets(): To access result sets returned by the procedure.
getProcOutVariables(): To access OUT or INOUTvariables returned by the procedure.

Syntax

storedprocService.execute(attribute-value pair)

Arguments

All attributes supported by the cfstoredproc tag.

setAttributes

Description	Sets attributes for the storedproc function.
Returns	Nothing
Syntax	storedProcService.setAttributes (attribute-value pair)
Arguments	All attributes supported by the cfstoredproc tag.

getAttributes

Description	Gets attributes that were set for the storedproc function.
Returns	Returns a struct with all or some of the attribute values.
Syntax	storedProcService.get_Attributes_ (attributelist)
Arguments	A comma-separated list of attributes. If no list is specified, all defined attributes are returned.

clearAttributes

Description	Removes all attributes added for the storedProc function.
Returns	Nothing
Syntax	storedProcService.clearAttributes(attribute_list)
Arguments	A comma-separated list of attributes.

clearParams

Description	Removes cfprocparam tags added using the addParam method.
Returns	Nothing
Syntax	storedProcService.clearParams()
Arguments	None

clearProcResults

Description	Removes cfprocresult tags added using the addProcResults method.
Returns	Nothing
Syntax	storedProcService.clearProcResults()
Arguments	None

clear

Description	Removes all attributes and params that were added using the methods addProcResults and addParam.
Returns	Nothing
Syntax	storedProcService.clear()
Arguments	None

Usage

This function corresponds to the cfstoredproc tag. For usage details, refer to the Usage section for cfstoredproc.

Example

<cfscript>
//If submitting a new book, insert the record and display confirmation
if(isDefined("form.title"))
{
//create a new storedproc service
spService = new storedproc();
//set attributes using implicit setters
spService.setDatasource("books");
spService.setProcedure("Insert_Book");
//add procparams using addParam
spService.addParam(cfsqltype="cf_sql_varchar", type="in",value=form.title);
spService.addParam(cfsqltype="cf_sql_numeric",type="in",value=form.price);
spService.addParam(cfsqltype="cf_sql_date", type="in",value=form.publishDate);
spService.addParam(cfsqltype="cf_sql_numeric",type="out",variable="bookId");
//add procresults using addProcResult
spService.addProcResult(name="rs1",resultset=1);
//execute the stored procedure
result = spService.execute();
//getprocOutVariables() returns any OUT or INOUT varibles added using addParams()
bookId = result.getprocOutVariables().bookId;
//getProcResultSets() returns resultsets added using addProcresult()
listOfBooks = result.getProcResultSets().rs1;
WriteOutput("<h3>List of Books</h3>");
writeDump(listOfBooks);
//output data
WriteOutput("<h3>" & "'" & form.title & "'" & " inserted into database. The ID is " & bookId & ".</h3>");
}
</cfscript>
<cfform action="#CGI.SCRIPT_NAME#" method="post">
<h3>Insert a new book</h3>
<table>
<tr>
<td>Title:</td>
<td><cfinput type="text" size="20" required="yes" name="title"/></td>
</tr>
<tr>
<td>Price:</td>
<td><cfinput type="text" size="20" required="yes" name="price" validate="float" /></td>
</tr>
<tr>
<td>Publish Date:</td>
<td>
<cfinput type="datefield" name="publishdate" mask="mm/dd/yyyy" size="20" ></td>
</tr>
</table>
<input type="submit" value="Insert Book"/>
</cfform>

Adobe

Get help faster and easier

New user?

storedproc

Description

Syntax

Properties

See also

History

Methods

Usage

Example

Get help faster and easier

Ask the Community

Contact Us

Description	Used to execute a stored procedure.
Returns	A component on which the following methods can be invoked:

Syntax	storedprocService.execute(attribute-value pair)
Arguments	All attributes supported by the cfstoredproc tag.