Whatever message this page gives is out now! Go check it out!

storedproc

Last update:
May 18, 2026
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_)

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,
<cfstoredproc procedure= "sp_proc">
can be used as
spService.setProcedure("sp_proc");
For details of the cfstoredproc tag attributes, see the Attributes section for cfstoredproc__.

See also

cfstoredproc, Function summary

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>

Share this page

Was this page helpful?
We're glad. Tell us how this page helped.
We're sorry. Can you tell us what didn't work for you?
Thank you for your feedback. Your response will help improve this page.
Previous page Script Functions Implemented as CFCs
Next page JavaScript object overview

On this page