Using cfstoredproc

Using `<cfstoredproc>`

<cfstoredproc> can be used to execute existing stored procedures. The tag cannot, however, be used to create stored proceduressomething <cfquery> can do.

<cfstoredproc> has two child tags that are used to pass parameters into the stored procedure and to receive data back. Parameters are sent to the stored procedure by the <cfprocparam> tag and can be received by either <cfprocparam> or <cfprocresult>. The most efficient way to receive record sets from a stored procedure is to use the <cfprocresult> tag because it returns data as a record set.

`<cfprocparam>`

Most stored procedures depend on parameters that are sent from the client application. For each parameter, a <cfprocparam> tag is used. You must know each parameter's data type as it is declared in the stored procedure. This data type is passed as the required attribute cfsqltype of the <cfprocparam> tag. Valid types are:

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

Be careful to correctly match the type to the actual data type defined in the stored procedure itself.

Another required attribute of <cfprocparam> is the type attribute. It has three different values, as shown in Table 23.1, which affect other attributes in various ways.

Table 23.1. Options for the `type` Attribute of `<cfprocparam>`
VALUE	DESCRIPTION
`in`	The parameter is expected by the stored procedure, and you are passing the parameter.
`out`	A result is expected from the stored procedure, and the `<cfprocparam>` tag will be used to create a variable of a specific name to accept this returned data.
`inout`	The names of the parameters sent and received are exactly the same. If they were not, you would have used a `<cfprocparam>` for the `IN` parameter and a second `<cfprocparam>` for the `out` parameter.

The <cfprocparam> tag's VALUE attribute is used only when sending parameters into the stored procedure using type="in". This process is pretty intuitive. Alternatively, the variable attribute works only when it's getting parameters out of a stored procedure using out, and represents the ColdFusion variable name holding the returned data. Finally, the dbvarname attribute passes the correct name of the stored procedure's variable, such as @RET in the stored procedure example at the beginning of this chapter.

TIP

At first glance, the <cfprocparam> tag appears to be the best way to get data in and out of a stored procedure. In reality, it is rarely used to get data out of a stored procedure. The tag was once the only solution available for Oracle developers, since the <cfprocresult> tag was not available. But because <cfprocresult> now works with Oracle, using it to attack most requirements is more efficient. This tag is covered in the next section.

NOTE

When more than one record sets is sent back from a <cfprocresult> tag, it is exposed as an array.

`<cfprocresult>`

The <cfprocresult> tag is the final piece of the stored procedure puzzle. When a stored procedure is executed, a record set may be returned to ColdFusion. The only required attribute of the tag is name, which creates the record set variable in ColdFusion. The following example shows the tag in action:

 <cfstoredproc procedure="CheckInventory"               datasource="dsn"> <!--- send the parameter ---> <cfprocparam type="IN"              cfsqltype="CF_SQL_CHAR"              value="in stock"              dbvarname="@param1">   <!--- get the result back --->   <cfprocresult name="qItemsInStock">   <!--- Note that cfstoredproc has an END tag! ---> </cfstoredproc> <!--- here is what is in inventory ---> <cfoutput query="qItemsInStock">  #productName#<br> </cfoutput>

Multiple Record Sets

A very useful feature of stored procedures is their capability to return multiple record sets to the client. ColdFusion supports this capability by giving different numbers to the <cfprocresult> tag to identify each given record set.

For this purpose, the resultset attribute is used. Each result set must be addressed specifically by its number, or it will not be exposed. If, for example, a stored procedure returns ten record sets and the resultset attribute is not specified, only the first record set is returned. To take the example further, if the resultset attribute is set to "10", only the last record set is returned. Now consider the following example:

 <!--- checkInventory returns multiple record sets ---> <cfstoredproc procedure="CheckInventory"               datasource="mySqlServer">   <!--- send the parameter --->   <cfprocparam type="IN"                cfsqltype="CF_SQL_CHAR"                value="in stock"                dbvarname="@param1">   <!--- get the FIRST result back --->   <cfprocresult name="MoreThan100InStock">   <!--- get the SECOND result back --->   <cfprocresult name="LessThan100InStock"                 resultset="2"> </cfstoredproc> <!--- here is what is in inventory ---> <cfoutput query="MoreThan100InStock">  #productName#<br> </cfoutput> <cfoutput query="LessThan100InStock">  #productName#<br> </cfoutput>

NOTE

Another optional attribute of the <cfprocresult> tag is maxrows. This attribute limits the number of rows that come back from the stored procedure, functioning in exactly the same way as the identically named <cfquery> attribute does.