cfquery Versus cfstoredproc


<cfquery> Versus <cfstoredproc>

To execute a stored procedure, you can use either the <cfquery> or <cfstoredproc> tag. Each has its own advantages and disadvantages.

<cfquery> is the simpler of the two, while <cfstoredproc> is more powerful and provides access to otherwise inaccessible features. For example, stored procedures can return record sets to the ColdFusion template more efficiently through the <cfstoredproc> tag. This tag also provides support for multiple record sets returned from a stored procedure, meaning that you can create more than one record set from the same procedure. Finally, some variables, such as the status code that is created when the tag is called, are not available through a <cfquery>.

Using <cfquery>

The SQL for running a stored procedure operates by referencing the stored procedure's name after the EXEC statement, followed by parameters. The following example shows how ColdFusion would execute a stored procedure through the use of the <cfquery> tag:

 <cfquery name="CheckInventory" datasource="dsn">  EXEC spCheckInventory  @Status = 'in stock' </cfquery> <!--- see what the status is and tell the user ---> <cfif CheckInventory.ret>  That item is in inventory! <cfelse>  Sorry, out of stock! </cfif> 

This method of calling stored procedures is perfectly valid and usable, yet the <cfstoredproc> tag has a few advantages, which will be discussed in the next section.

NOTE

With some DBMSs, the use of the keyword EXEC in the above code example is optional. And yet other DBMSs do not allow EXEC based execution at all.




Macromedia ColdFusion MX 7 Certified Developer Study Guide
Macromedia ColdFusion MX 7 Certified Developer Study Guide
ISBN: 0321330110
EAN: 2147483647
Year: 2004
Pages: 389
Authors: Ben Forta

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net