-1

I have a db2 stored procedure that takes in some parameters, gets some data from somewhere and then returns a result set through a cursor.

Now I want to write a table function in db2, that will call this stored procedure, read from the result set and return the data in the result set as a table (eventually I want to use this table function in a join).

I would like to know if this is permitted in db2 (we're using DB2 v10.5), i.e. execute a stored procedure in a table function and fetch and read from the result set from the stored procedure. If so, what is the right syntax for calling the stored procedure and reading the result set inside a table function in db2? Thanks!

Carrington C
  • 1
  • 1
  • 1
  • Welcome to SO! Please read [how-to-ask](https://stackoverflow.com/help/how-to-ask) and correct your question due to instruction. If you need code samples for learning, please use web search engine first, for example Google. – Maciej S. Mar 19 '19 at 01:42
  • I'm voting to close this question as off-topic because it's cross-posted on dba.stackexchange.com – mustaccio Mar 19 '19 at 10:57
  • Yeah sorry my bad. I also posted the same question on stack exchange: https://dba.stackexchange.com/questions/232475/db2-how-to-call-a-stored-procedure-that-returns-a-result-set-in-another-user-d – Carrington C Mar 19 '19 at 15:42

2 Answers2

1

Yes, it's possible. See the example below.

--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE TEST_PROC(P_TABSCHEMA VARCHAR(128))
DYNAMIC RESULT SETS 1
READS SQL DATA
BEGIN
  DECLARE C1 CURSOR WITH HOLD WITH RETURN FOR
    SELECT TABSCHEMA, TABNAME, COLCOUNT
    FROM SYSCAT.TABLES
    WHERE TABSCHEMA=P_TABSCHEMA;
  OPEN C1;
END@

--CALL TEST_PROC('SYSCAT')@

CREATE OR REPLACE FUNCTION TEST_PROC(P_TABSCHEMA VARCHAR(128))
RETURNS TABLE (
  TABSCHEMA VARCHAR(128)
, TABNAME VARCHAR(128)
, COLCOUNT INT
)
READS SQL DATA
BEGIN
  DECLARE SQLSTATE CHAR(5);
  DECLARE V_TABSCHEMA VARCHAR(128);
  DECLARE V_TABNAME VARCHAR(128);
  DECLARE V_COLCOUNT INT;
  DECLARE V1 RESULT_SET_LOCATOR VARYING;

  CALL TEST_PROC(P_TABSCHEMA);
  ASSOCIATE RESULT SET LOCATOR (V1) WITH PROCEDURE TEST_PROC;
  ALLOCATE C1 CURSOR FOR RESULT SET V1;

  L1: LOOP
    FETCH C1 INTO V_TABSCHEMA, V_TABNAME, V_COLCOUNT;
    IF SQLSTATE<>'00000' THEN LEAVE L1; END IF;
    PIPE(V_TABSCHEMA, V_TABNAME, V_COLCOUNT);
  END LOOP L1;
  CLOSE C1;
  RETURN;
END@

SELECT * FROM TABLE(TEST_PROC('SYSCAT'))@
Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
  • 1
    For Db2-LUW 11.1.4.4 on Linux, calling the pipelined table-function results in SQL20148N ..must have the RETURN statement as the last SQL statement of the compound body. However, the result-set is still returned! – mao Mar 19 '19 at 09:29
  • @mao The same is for 10.5.8. I overlooked the exception. I've edited the example. Thanks! – Mark Barinstein Mar 19 '19 at 09:43
1

You need to create the DB2 table-function as follows:

CREATE FUNCTION database_schema.function_name ( IN_PARTID VARCHAR(1000) ) 
RETURNS TABLE ( PARTNO CHAR(25), PARTDS CHAR(30), QUANTITY INT )   

BEGIN 
RETURN SELECT PARTNO , PARTDS , CASE WHEN QUANTITY > 0 THEN QUANTITY ELSE 0 END QUANTITY 
FROM 
( 
    SELECT PARTNO 
    ,MAX(PARTDS) AS PARTDS       
    ,SUM(QUANTITY) AS QUANTITY 
    FROM database_schema.table_name
    WHERE 1=1
    AND PARTID = (CAST(IN_PARTID AS INT))
    GROUP BY PARTNO  
) AA; 
END;

Then invoke the table-function as join or straight SQL:

SELECT partno,partds,quantity
FROM TABLE(database_schema.function_name('parameter_1'))
JPC
  • 11
  • 1