Stored Procedures | Inside Coldfusion MX

A stored procedure is defined as a DBMS operation, which is typically a set of SQL statements that is stored on the database server. Stored procedures are a method of encapsulating repetitive tasks and improving performance.

Although a detailed discussion of stored procedures is beyond the scope of this chapter, you should be familiar with the advantages of using stored procedures and how to utilize them with ColdFusion. Stored procedures can do the following:

Improve performance. Provides a single execution plan on the server. A stored procedure is precompiled and residing on the database server.
Share application logic between applications. We recently developed a web site that utilized a web interface that was developed with ColdFusion. The existing application was a Visual Basic application that utilized the same stored procedures. This capability to share code between applications helped to ensure that both applications used the same business logic. In addition, if changes were required to the existing business logic, the changes could be effected in a single place the stored procedure.
Shield users from details of the tables in the database. The user is not required to know any details about tables to execute the stored procedure, only parameters passed and values returned. Because stored procedures are often used to execute business logic, this enables changes to be made to the business logic, and even the table schemas, without changing the code ColdFusion is using to call the stored procedure.
Provide security. The user can be granted permission to execute a stored procedure without having to be granted access to tables.
Reduce network traffic. Eliminates the need to send complex SQL statements.

There are two ways to execute stored procedures in ColdFusion: the CFSTOREDPROC and CFQUERY tags. The following syntax is used to execute a stored procedure with the CFQUERY tag.

 <cfquery NAME="TestQuery" DATASOURCE="TestDNS"  (Execute database.sp_testsp  @var1 = '#CustomerFirstName#',  @var2 = '#CustomerLastName#')  </cfquery>

When executing a stored procedure using the CFQUERY tag, you face a number of limitations. One of the major limitations is that you have no way to access return codes or output parameters that are created by the stored procedure. To use CFQUERY to execute a stored procedure, you must have a native-drive datasource.

CFSTOREDPROC

The preferred method for executing a stored procedure is the CFSTOREDPROC tag. The CFSTOREDPROC tag executes stored procedures using either an ODBC or native connection to a server database. It specifies database connection information and identifies the stored procedure. The CFSTOREDPROC tag has two required attributes: procedure and datasource.

There are also several optional attributes, many of which you have already encountered with other ColdFusion tags. The one optional attribute that might not be familiar is returncode. When a stored procedure is executed using CFSTOREDPROC, two return values, cfstoredproc.statuscode and cfstoredproc.executiontime, are automatically generated by ColdFusion. When the returncode is Yes, the value of cfstoredproc.statuscode is set to the status code returned by the stored procedures. An example of use of the CFSTOREDPROC tag is shown in the following code:

 <cfstoredproc PROCEDURE = "sp_test"  DATASOURCE="TestDNS" RETURNCODE = "YES">

The CFSTOREDPROC tag simply executes the desired stored procedure; it uses the child tags CFPROCPARAM and CFPROCRESULT to pass parameters and receive result sets.

CFPROCPARAM

The CFPROCPARAM tag is nested within a CFSTOREDPROC tag and is used to pass parameters to the stored procedure. This tag is used to identify parameters and their data types. You need to provide one CFPROCPARAM tag for each parameter. Only one attribute is required in the CFPROCPARAM tag: CFSQLTYPE.

The CFSQLTYPE attribute specifies the SQL type to which the parameter is bound. The following list presents the allowable values:

CF_SQL_BIGINT
CF_SQL_BIT
CF_SQL_BLOB
CF_SQL_CHAR
CF_SQL_CLOB
CF_SQL_DATE
CF_SQL_DECIMAL
CF_SQL_DOUBLE
CF_SQL_FLOAT
CF_SQL_IDSTAMP
CF_SQL_INTEGER
CF_SQL_LONGVARCHAR
CF_SQL_MONEY
CF_SQL_MONEY4
CF_SQL_NUMERIC
CF_SQL_REAL
CF_SQL_REFCURSOR
CF_SQL_SMALLINT
CF_SQL_TIME
CF_SQL_TIMESTAMP
CF_SQL_TINYINT
CF_SQL_VARCHAR

The type attribute has three options: "in", "out", and "inout". It specifies whether the parameter is being passed in or out. The option of "inout" is used when a parameter with the identical name is being passed both in and out.

There are a number of optional attributes that become required and dependent upon the use of other optional attributes. When the attribute TYPE is specified as "in" or "inout", the attribute "value" is required. The "value" attribute corresponds to what is passed to the stored procedure. When the type attribute is "out" or "inout", the attribute "variable" is used to represent the data returned by the stored procedure.

 <cfprocparam TYPE = "in"        CFSQLType = "CF_SQL_VARCHAR"   DBVARNAME = "FirstName"        VALUE = "#CustomerFirstName#">

Although the CFPROCPARAM tag offers a way to get data from a stored procedure, the recommended way is with the CFPROCRESULT tag.

CFPROCRESULT

The CFPROCRESULT tag enables you to specify a name for the results set returned by a stored procedure. This enables you to then call the result set with other ColdFusion tags. The only required attribute is the name attribute. If a stored procedure returns more than one result set, you need to use the resultset attribute.

 <cfprocresult NAME = "CustomerID"  resultSet = "1" >  <cfstoredproc procedure="sp_ProcessOrder" datasource="rits" returncode="yes">    <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@OrderNumber"  value="#FORM.OrderNumber#" null="No">  <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@CustomerID"  value="#FORM.CustomerID#" null="No">  <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@ProductID"  value="#FORM.ProductID#" null="No">  <cfprocparam type="In" cfsqltype="CF_SQL_TIMESTAMP" dbvarname="@OrderDate"  value="#FORM.OrderDate#" null="No">  … more code  </cfstoredproc>  <cfquery name="custdetail" datasource="rits">        SELECT CustomerID, CustomerFirstName, CustomerLastName        FROM Customer        WHERE CustomerID = #Form.CustomerID#  <cfoutput>      <cfif CFSTOREDPROC.STATUSCODE EQ '-1'>                 The order was not processed, the return code for the stored procedure  is: #CFSTOREDPROC.STATUSCODE#           <cfelse>                Order number #Form.OrderID# for #custdetail.CustomerFirstName#  #custdetail.CustomerLastName# , Customer ID #custdetail.CustomerID# has been  sucessfully processed.           </cfif>  </cfoutput>

Summary

Stored procedures are a named set of SQL statements that are stored on the database server. They offer a method of encapsulating repetitive tasks. This translates to improved performance, code reuse, reduced network traffic, and security benefits. ColdFusion enables two methods for executing stored procedures: the CFQUERY and CFSTOREDPROC tags. The CFQUERY approach cannot access return codes or output from the stored procedure, and it requires the use of a native driver. The CFSTOREDPROC tag uses the child tags of CFPROCPARAM and CFPROCRESULT. The CFPROCPARAM tag is used to identify stored procedure parameters and their type. A CFPROCPARAM tag is required for each parameter passed to or returned by the stored procedure. The CFPROCRESULT tag provides a name by which other ColdFusion tags can access stored procedure results.