Stored Procedures

Stored procedures are SQL statements that are stored on the DBMS server itself. Instead of entering SQL directly, you can call the stored procedure by name to execute the stored SQL.

Stored procedures are invaluable for several reasons:

• Stored procedures execute more quickly than straight SQL statements because the code saved on the server is already parsed and ready for use.
  
  
• Stored procedures may contain multiple SQL operations, which enables you to call a single command and execute a set of statements (without having to specify them individually).
  
  
• Depending on the DBMS being used, stored procedures might be able to return multiple result sets as well as output parameters.
  
  
• Stored procedures can be used to hide complex data structures, so users can call the procedures and pass them values as needed, and the procedures can internally manipulate tables as required.
  
  
• Stored procedures can be used to secure underlying data. Users might not be given access to underlying tables (to prevent abuse or misuse); instead, they communicate with a stored procedure that in turn interacts with the underlying tables.
  
  

There are lots of reasons to use stored procedures, which are created using the SQL CREATE PROCEDURE statement (which varies dramatically from one database to the next).

For information about invoking stored procedures from within your ColdFusion code, see Chapter 23, "Stored Procedures."