How to write a Stored Procedure in DB2 for SELECT operation ? How can I fetch all records from a table using Stored Procedure?

Question

I want a proper stored procedure for SELECT operation in DB2 Database.

Want to fetch all records from a specific table using stored procedure.

This is my Table Structure -

create table emp2 (int_1 int, char_1 char(10))

Below is my Stored Procedure for SELECT operation in DB2 -

@delimiter %%%;
CREATE OR REPLACE PROCEDURE "DB2INST1"."SELECT_1" (OUT int_1 int, OUT char_1 char(10)) 
SPECIFIC SELECT_1
LANGUAGE SQL
DYNAMIC RESULT SETS 1

BEGIN

select * from emp2;

END;%%%
@delimiter;%%%

This procedure is not working.

Please let me know the correct syntax for SELECT operation using Stored Procedure.

Answer 1

I assume this is for educational purposes. First. your out parameters can only hold one value, so unless your tables contains at most one row, you need a cursor.

Second, I don't think you can use a string as statement delimiter, it needs to be a character. You can do this as:

--#SET TERMINATOR @

Something like:

CREATE OR REPLACE PROCEDURE DB2INST1.SELECT_1 ()
SPECIFIC SELECT_1
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
    declare c1 cursor with return to client for select * from emp2;
    open c1;
END
@