Application Development Guide

Comparison of SQL Procedures and External Procedures

Like external stored procedure definitions, SQL procedure definitions provide the following information:

• The procedure name.
• Parameter attributes.
• The language in which the procedure is written. For an SQL procedure, the language is SQL.
• Other information about the procedure, such as the specific name of the procedure and the number of result sets returned by the procedure.

Unlike a CREATE PROCEDURE statement for an external stored procedure, the CREATE PROCEDURE statement for an SQL procedure does not specify the EXTERNAL clause. Instead, an SQL procedure has a procedure body, which contains the source statements for the stored procedure.

The following example shows a CREATE PROCEDURE statement for a simple stored procedure. The procedure name, the list of parameters that are passed to or from the procedure, and the LANGUAGE parameter are common to all stored procedures. However, the LANGUAGE value of SQL and the BEGIN...END block, which forms the procedure body, are particular to an SQL procedure.

   CREATE PROCEDURE UPDATE_SALARY_1         (1)
   (IN EMPLOYEE_NUMBER CHAR(6),             (2)
   IN RATE INTEGER)                         (2)
   LANGUAGE SQL                             (3)
   BEGIN
      UPDATE EMPLOYEE                       (4)
      SET SALARY = SALARY * (1.0 * RATE / 100.0 )
      WHERE EMPNO = EMPLOYEE_NUMBER;
   END

Notes for the previous example:

(1)	The stored procedure name is UPDATE_SALARY_1.
(2)	The two parameters have data types of CHAR(6) and INTEGER. 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.

Within the SQL procedure body, you cannot use OUT parameters as a value in any expression. You can only assign values to OUT parameters using the assignment statement, or as the target variable in the INTO clause of SELECT, VALUES and FETCH statements. You cannot use IN parameters as the target of assignment or INTO clauses.