Differences between native SQL procedures and external procedures

SQL procedures are written entirely in SQL statements. External procedures are written in a host language and can contain SQL statements. You can invoke both types of procedures with an SQL CALL statement. However, you should consider several important differences in behavior and preparation.

Native SQL procedures and external procedures differ in the following ways:

How they handles errors

For an SQL procedure, Db2 automatically returns SQL conditions in the SQLCA when the procedure does not include a RETURN statement or a handler. For information about the various ways to handle errors in an SQL procedure, see Handling SQL conditions in an SQL procedure.
For an external stored procedure, Db2 does not return SQL conditions in the SQLCA to the invoking application. If you use PARAMETER STYLE SQL when you define an external procedure, you can set SQLSTATE to indicate an error before the procedure ends. For valid SQLSTATE values, see SQLSTATE values and common error codes.

How they specify the code for the stored procedure

SQL procedure definitions contain the source code for the stored procedure. An external stored procedure definition specifies the name of the stored procedure program.

How you define the stored procedure.

For both native SQL procedures and external procedures, you define the stored procedure to Db2 by executing the CREATE PROCEDURE statement. For external procedures, you must also separately bind the source code for procedure into a package. You can do this before or after you issue the CREATE PROCEDURE statement to define the external procedure.

Examples

Creating a native SQL procedure

The following example shows a definition for an SQL procedure.

CREATE PROCEDURE UPDATESALARY1         1 
 (IN EMPNUMBR CHAR(10),                2 
 IN RATE DECIMAL(6,2))
 LANGUAGE SQL                          3 
  UPDATE EMP                           4 
   SET SALARY = SALARY * RATE
   WHERE EMPNO = EMPNUMBR

Notes:

1: The stored procedure name is UPDATESALARY1.
2: The two parameters have data types of CHAR(10) and DECIMAL(6,2). Both are input parameters.
3: LANGUAGE SQL indicates that this is an SQL procedure, so a procedure body follows the other parameters.
4: The procedure body consists of a single SQL UPDATE statement, which updates rows in the employee table.

Creating an external stored procedure

The following example shows a definition for an equivalent external stored procedure that is written in COBOL. The stored procedure program, which updates employee salaries, is called UPDSAL.

CREATE PROCEDURE UPDATESALARY1         1 
 (IN EMPNUMBR CHAR(10),                2 
 IN RATE DECIMAL(6,2))
 LANGUAGE COBOL                        3 
 EXTERNAL NAME UPDSAL;                 4

Notes:

1: The stored procedure name is UPDATESALARY1.
2: The two parameters have data types of CHAR(10) and DECIMAL(6,2). Both are input parameters.
3: LANGUAGE COBOL indicates that this is an external procedure, so the code for the stored procedure is in a separate, COBOL program.
4: The name of the load module that contains the executable stored procedure program is UPDSAL.