There are of course pros and cons to using each method to perform CRUD operations on a database.
cfstoredproc Pros:
- With stored procedures you can control security in the database as well as the web pages.
- Transactions are handled at the database level, stored procedures are ideal here.
- Stored procedures provide greater performance, because the execution plan for the sql doesn’t have to be recreated and if a page has multiple calls to the database, you can combine them all in one stored procedure, meaning only one trip to and back from the db.
- Putting all of your sql in stored procedures provides centralization of your database code, so if your table structure or business rules change, all of your sql is located in your database stored procedures instead of distributed throughout your application code.
cfstoredproc Cons
You can have ColdFusion cache the query - which it doesn’t do with stored procedures.Update Nov 2006: you can actually cache stored procedures! See here for more info- It often seems a waste to create a stored proc for a simple select statement.
- cfquery gives you maximum flexibility in terms of creating the sql for the query. there are many ways to make stored procedures “dynamic”, such as providing parameters for a where clause, the fields used in an order by clause, etc., but many of these sql solutions are clunky (involving multiple CASE statements) or impossible (dynamically assigning the table to do the query on).
sir i wanna to know about cfstoredproc
1. plz descibe how to use in cold fusion if i create a storeprocedure in sqlserver ,oracle ,
2. is it poosible to create a store procedure in ms access if yes the write me all procee and how to use in cold fusion
arvind
January 11th, 2007
[...] using a stored procedure (there are many, many positive reasons for using a stored procedure. See here and here for more [...]
chapter31 » Combining insert and update SQL logic
April 11th, 2007