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