Systems Engineering and RDBMS

DB2 LUW – Returning a recordset from a stored procedure

Posted by decipherinfosys on August 13, 2007

In one of our earlier posts, we had covered how one can return result-sets from a stored procedure in Oracle. In this post, we are going to cover the same for DB2 LUW. DB2 LUW also does not require any variable declaration to pass back to the calling program. But we need to DECLARE the cursor first and then OPEN the cursor. When we declare a cursor we have to use WITH RETURN TO clause in order to return record set from the procedure. If result set is to be returned to invoker of the procedure then we need to use WITH RETURN TO CALLER in declaration. If result set is to be returned to originating application then we need to use WITH RETURN TO CLIENT in declaration. Here we are assuming that we are returning result set to originating application and hence we are using WITH RETURN TO CLIENT.

Let us create table first and populate it with data. You can create it either via command editor or using command window. Please make sure that statement terminator is “@” or else you may receive an error (or replace with whatever statement terminator you have set in your programming tool).

CREATE TABLE Invoice
(
INVOICE_NUMBER INTEGER NOT NULL GENERATED BY DEFAULT AS
IDENTITY (START WITH +1, INCREMENT BY +1, CACHE 1000),
INVOICE_DATE TIMESTAMP NOT NULL,
CLIENT_ID INTEGER NOT NULL,
INVOICE_AMT DECIMAL(9,2) DEFAULT 0 NOT NULL,
PAID_FLAG SMALLINT DEFAULT 0 NOT NULL,
CONSTRAINT PK_INVOICE PRIMARY KEY(INVOICE_NUMBER)
)
@

INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp,101,1100.00)@
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp,102,1100.00)@
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp,103,1100.00)@
INSERT INTO INVOICE(Invoice_date, client_ID, Invoice_Amt) VALUES(current timestamp,104,1100.00)@

Following is the procedure to return the result set. You can run it from Command Editor or save it in a file and run it from command window using following command.

db2 �td@ -f <filename> (filename in which following code is stored).

CREATE PROCEDURE DEC_RTN_RECORDSET
(
IN P_InvoiceDate TIMESTAMP
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
–Get the data from the table to display it back to client application
BEGIN
DECLARE c2 CURSOR WITH RETURN TO CLIENT FOR
SELECT Invoice_Number, Invoice_Date, Client_ID, Invoice_Amt
FROM Invoice
WHERE Invoice_date <= p_InvoiceDate
ORDER BY Invoice_number;

OPEN c2;
END;
END
@

— To test the procedure execute following command.
call DEC_RTN_RECORDSET(current timestamp)@

Following is the output. It is formatted for more readability.

Invoice_Number Invoice_Date Client_ID Invoice_Amt
————– ———— ——— ———–
1 2007-01-25-15.45.33.991000 101 1100
2 2007-01-25-15.45.34.054000 102 1100
3 2007-01-25-15.45.34.132000 103 1100
4 2007-01-25-15.45.34.210000 104 1100

Sorry, the comment form is closed at this time.

 
%d bloggers like this: