Just to add a bit to the thread: cfstoredproc is, in general, a little more secure. It allows you to specify the data type of parameters, which can help with security. Stored procedures are generally harder to "break out of" during a SQL injection attack. Also, a stored procedure can have more specificity with regards to permissions and accessing other databases.
CFQuery is more flexible -- you can just type in the SQL you want to run. Cfstoredproc requires you to create and modify stored procedures in the db. It's not a big deal, but it's kind of a pain when you are developing an app or making changes -- especially if someone else is doing the db work. None of these are absolute reasons to use one over the other. For example, you can set up a way to check parameters before passing them to cfquery. CFStoredproc comes with them out of the box, as it were. I've found that they work really well together. I generally develop an app with cfquery, and change them all to cfstoredproc before I put it into production. Jeff Polaski Webmaster Office of Research Office of Graduate Studies University of California, Irvine http://www.rgs.uci.edu/ 949.824.6363 -----Original Message----- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Thursday, February 22, 2007 9:44 AM To: CF-Talk Subject: RE: cfstoredproc vs cfquery > Anyone know if there is an advantage either way when it comes > to calling a stored procedure? In the example you provided, there's no difference, but stored procedures can be much more complex than a single SQL statement. Stored procedures can return multiple recordsets, they can accept and return parameters. The CFSTOREDPROC tag allows for all that. Also, you can't use CFQUERY on all platforms to execute stored procedures, as I recall. But if you don't have any of these issues there's nothing wrong with using CFQUERY instead, and there are some advantages, such as the ability to use CACHEDWITHIN/CACHEDAFTER. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270495 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
