Contents > CFML Reference > ColdFusion Tags > cfstoredproc

cfstoredproc

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

Database manipulation tags

<cfstoredproc 
   procedure = "procedure name"
   dataSource = "ds_name"
   username = "username"
   password = "password"
   blockFactor = "blocksize"
   debug = "Yes" or "No"
   returnCode = "Yes" or "No">

cfinsert, cfqueryparam, cfprocparam, cfprocresult, cftransaction, cfquery, cfupdate

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. (ColdFusion MX uses Type 4 JDBC drivers.)

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. Additionally, you must code cfprocparam tags in the same order as the parameters in the stored procedure definition.

If you set returnCode = "Yes", this tag sets the variable cfstoredproc.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 cfstoredproc.ExecutionTime, which contains the execution time of the stored procedure, in milliseconds.

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 stored procedure, see cfprocparam.

<!--- 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. --->
<!--- 
<cfstoredproc procedure = "foo_proc"
   dataSource = "MY_SYBASE_TEST" username = "sa"
   password = "" dbServer = "scup" dbName = "pubs2"
   returnCode = "Yes" debug = "Yes">
   <cfprocresult name = RS1>
   <cfprocresult name = RS3 resultSet = 3>

   <cfprocparam type = "IN"       CFSQLType = CF_SQL_INTEGER
         value = "1" dbVarName = @param1>
   <cfprocparam type = "OUT" CFSQLType = CF_SQL_DATE
      variable = FOO dbVarName = @param2>
</cfstoredproc>
--->
<!--- 
<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>
--->

Contents > CFML Reference > ColdFusion Tags > cfstoredproc

ColdFusion 8 | ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | Bug Reporting

Version 6.1

Comments are no longer accepted for ColdFusion MX 6.1. ColdFusion 8 is the current version.

Comments

Add Comment

wyntermoonwolf said on Apr 30, 2004 at 10:54 AM :

The documentation for cfprocparam states:
Changed the dbvarname attribute behavior: it is now ignored for all drivers. ColdFusion MX uses JDBC 2.2 and does not support named parameters.

The example should be modified not to use it. The example given came from the CF5 docs.

jrunrandy said on Apr 30, 2004 at 11:04 AM :

Good catch. We'll fix this in the next CF rev. Thanks.

PaulArbouw said on Nov 4, 2004 at 11:53 AM :

since dbvarname no longer works.. how do I tell the stored procedure what the value is of the variables that it uses?

jrunrandy said on Nov 4, 2004 at 1:44 PM :

Arguments are positional without dbvarname.

If you pass the first, second, third, etc. arguments (with cfprocparam) in the correct order - they will match the stored procedure.

With CF5 & dbvarname, you could pass arguments out of order if you wanted to.

For example:

A stored procedure takes 3 arguments: NAME, ADDRESS, ZIP

With CFMX you must pass these arguments in the same order - NAME, ADDRESS, ZIP.

With CF5 you could pass the zip code first if you wanted to.

codyfox said on Oct 5, 2005 at 9:47 AM :

What I am finding hard to believe is that there was a logical reason for changing this and not changing the documentation anywhere except at the bottom of this page. Even the Coldfusion help files on Dreamweaver 8 have the old examples of using the <cfstoredproc> tag. The error that is generated from SQL server is obscure and nearly impossible to debug. This should not be changed at a further date, this should be changed now!

RSS feed | Send me an e-mail when comments are added to this page | Comment Report

Current page: http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-pc9.htm

Add Comment