Defining and Using Procedures › When to Use a Procedure

When to Use a Procedure

SQL-invoked procedures implement a remote procedure call paradigm. They have similar uses as table procedures, but generally cannot replace table procedures because of their inability to return many rows of a result table with the table procedure parameters as columns of a table. Instead, they return only 0 or 1 row of parameters. Procedures are much simpler to program and can be written in the SQL procedural language.

Procedures can be implemented for many uses which include but is not limited to the following:

• Reuse existing code
• Encapsulate complex code
• Standardize common business processes
• Reduce communication bandwidth
• Easier and faster deployment and control of applications
• Isolate user interface logic from database access
• Implement transparently segmented databases
• Access non-SQL defined databases

While procedures cannot return more than one row from a result table, made up from the procedure parameters, it is possible for the caller of a procedure to receive result sets created by the procedure. The caller can allocate and process dynamic cursors for all the result sets returned by the called procedure. This feature is called dynamic result sets. For more information, see CALL.