How to use tablename as param in stored procedure DB2?

user_16551727 0
Viewed 52

I want to create a stored procedure that will perform something like this: select * from tablename. Using the parameter and call. Maybe my non-working example will show what I mean:

CREATE OR REPLACE PROCEDURE

    PROC( IN TABL_NAME varchar(255))
    SPECIFIC PROC
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1
    BEGIN
        DECLARE cur1 CURSOR WITH RETURN FOR 
        SELECT * FROM TABL_NAME;
    OPEN cur1;
    END;
    call PROC('employee');

The result of execution in this case should be of the type select * from employee

db2 stored-procedures sql
1 Answers

You must use dynamic sql in such a case.
S1 is of STATEMENT type and you don't have to declare this variable explicitly.

CREATE OR REPLACE PROCEDURE PROC (IN TABL_NAME varchar(255))
SPECIFIC PROC
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
    --DECLARE S1 STATEMENT;
    DECLARE cur1 CURSOR WITH RETURN FOR S1;
    
    PREPARE S1 FROM 'SELECT * FROM ' || TABL_NAME;
    OPEN cur1;
END
user_104182640
Related