DB2 Stored Procedure

In simple words Stored Procedure is defined as An application program, possibly containing SQL statements, that can be invoked with the SQL CALL statement.
OR
A user-written application program that can be started by using the SQL CALL statement.
Stored procedures are programs whose executable binaries reside at the database server.
They serve as subroutines to calling applications, and they normally wrap multiple SQL statements with flow logic.
 

In the figure, Program 1 and stored procedure mysp execute the same set of SQL statements. Program 1, however, does not perform as well as Program 2 because of the extra overhead of sending each SQL statement through the network and waiting for its return. On the other hand, Program 2 only needs to call the stored procedure mysp once and then wait for its return. Because mysp performs all the SQL statements within the database server, there is minimal network overhead.

Besides improving response time for applications running on a different server than the database server, stored procedures also provide a central location to store database application logic. This allows for a single place to maintain your code.

External Procedure

An application program written in a host language, possibly containing SQL statements, that can be started with the SQL CALL statement.

 CREATE PROCEDURE SAMPLE.EMP_NAME (IN p_empno CHAR(06),
                                                            OUT p_emp_name CHAR(30))
-----------------------------------------------------------------------------------------------
-- SQL Stored Procedure to get employee name based on emp no --
-----------------------------------------------------------------------------------------------
P1: BEGIN
DECLARE v_firstName VARCHAR(12);
DECLARE v_lastName VARCHAR(15);

SELECT lastname, firstnme INTO v_lastName, v_firstName
FROM employee WHERE empno = p_empno;

SET p_empName = v_lastName || ', ' || v_firstName;
END P1

Created with Artisteer