Determining what routine implementation to use

The choice of using or creating a routine with a built-in, sourced, SQL, or external routine implementation can influence what functionality the routine can provide, the performance of the routine, and the likelihood of runtime problems that might require debugging.

About this task

Whenever possible, if there is an existing built-in routine that provides the support that you require, use it. Use existing built-in routines whenever possible. If the functionality you require is very similar to that of an existing built-in function, consider creating a sourced function that extends it.

If you must create a routine, use the following procedure. It is important to determine what routine implementation to use before proceeding too far with routine design.


To determine whether to use a sourced, SQL, or external routine implementation when creating a routine:

  1. Determine whether you want to create a procedure, function, or method. This should always be your first step when developing a routine. Also determine what are the support implementations for that routine type. See:
  2. Determine what SQL statements you want to include in the routine. The set of SQL statements that you want to execute in a routine can limit your choice of routine implementation. See:
  3. Determine if now or in the future the routine logic must access data, files, or applications that reside external to the database. The data, files, or applications might reside in the file system of the database server or in the available network.
    • If the routine logic must access entities outside of the database, you must use an external routine implementation.
  4. Determine the number of queries to be included in the routine relative to the quantity of procedural flow logic.
    • If the routine logic contains primarily procedural flow logic and very few queries, create an external routine.
    • If the routine logic contains many queries and a minimal amount of procedural flow logic, create an SQL routine.