A Beginner's Guide to Using Stored Procedures with ColdFusion

Background and Benefits of Stored Procedures

Stored procedures are (typically small) programs that run in a language that is specific to a particular database server. The programming language varies from database server to database server, and is designed to extend SQL to offer additional functionality that’s not available through standard SQL syntax. Stored procedures offer a number of compelling benefits for enterprise-level applications: security, performance, and business logic abstraction. This article will help you understand how to use ColdFusion as a presentation layer for a set of existing stored procedures.

Security: Stored procedures offer the ability to restrict inputs and results. With stored procedures, your database administrator can limit access to sensitive information.

Performance: Database management systems have two halves: a query optimizer and an execution engine. The query optimizer reads a query and performs a lot of CPU-intensive work to parse the query and determine which indexes and join methods would be best to use for the tables in a query. The query plan created by the query optimizer is then passed to the execution engine, which follows the query plan and gathers the specified data, perform joins, and sort results. When using a stored procedure, the database can remember, or cache the query plan, which bypasses much of the work involved in carrying out queries.

Business logic abstraction: If multiple systems access the same database, then any change in the database structure can result in considerable work to rewrite all systems that use the tables affected by the change. By moving the business logic into stored procedures, a change in the underlying table structure can be masked by a rewrite of the stored procedure, so that it returns the correct data for the same input, even though the table structure has been altered.

Page 2 of 6

Previous Page

Next Page