A simple example about creating DB2 SQL stored procedure to return multiple Result Set

There are plenty of examples showing how to get multiple result sets in languages like Java or php. But, there are not so many simple stored procedure examples, which return multiple result sets. Here is a very simple DB2 stored procedure, which return two result set. Just following the steps, you will have a multiple results DB2 stored procedure for testing.

Step 1: Create a table:

CREATE TABLE DEPT
     (DEPTNO   CHAR(3)     NOT NULL,
      DEPTNAME VARCHAR(36) NOT NULL,
      PRIMARY KEY(DEPTNO))

Step 2: Populate data into table:

INSERT INTO DEPT (DEPTNO, DEPTNAME)
     VALUES ('B11', 'PURCHASING'),
            ('E41', 'DATABASE ADMINISTRATION') ;

Step 3: Create SQL store procedure:

CREATE PROCEDURE TESTMULTIRS
--We do not use this input parameter.
(IN i_cmacct CHARACTER(5)) 
RESULT SETS 2
LANGUAGE SQL
BEGIN 

DECLARE csnum INTEGER;

--Declare serial cursors as serial cursor consume less resources
--and we do not need rollable cursor.
DECLARE getDeptNo CHAR(50); --Be careful about estimated length here.
DECLARE getDeptName CHAR(200);
DECLARE c1 CURSOR WITH RETURN FOR s1; 
DECLARE c2 CURSOR WITH RETURN FOR s2;

SET getDeptNo = 'SELECT DEPTNO FROM DEPT';
SET getDeptName = 'SELECT DEPTNAME FROM DEPT'; 

PREPARE s1 FROM getDeptNo;
OPEN c1;

PREPARE s2 FROM getDeptName;
OPEN c2;

END;

Step 4: Call stored procedure in iNavigator:

call testmultirs('jia');

Then we can see outpu as below in inavigator:

and