CODEWITHSUNDEEP
sqlstored-proceduresdb2resultset
Applegate
Applegate

Reputation: 27

How to manipulate resultset returned by a stored procedure called inside the stored procedure in DB2

I need to create a stored procedure (SP_CALLER) that will call another stored procedure (SP_CALLED) and select its returned resultset.

How can we query the returned result set??

CREATE OR REPLACE PROCEDURE SCHEMANAME.SP_CALLER(@PARAM1 INT, @PARAM2 VARCHAR)
DYNAMIC RESULT SETS 1

P1:BEGIN

DECLARE VALUE1 INT DEFAULT 0;
DECLARE VALUE2 VARCHAR DEFAULT 'TEST';

CALL SCHEMANAME.SP_CALLED @XPARAM1=VALUE1, @XPARAM2=VALUE2; --<-- returns a result set

SELECT * 
INTO #TEMPTABLE
FROM (resultsetquery); --<-- from the returned result set of sp_called



SELECT *
FROM #TEMPTABLE a, 
SCHEMANAME.TABLE2 b
WHERE a.COL1 = @PARAM1 AND b.COL2 = @PARAM2;



END P1

UPDATE 04/04/2019:

The docs were really helpful, but i was stuck here.. how can i query the resultset that allocated to this variable ('cur') and return it as the new result set of my stored procedure (SCHEMANAME.SP_CALLER)???

CREATE OR REPLACE PROCEDURE SCHEMANAME.SP_CALLER() DYNAMIC RESULT SETS 1

P1:BEGIN

DECLARE loc_cursor RESULT_SET_LOCATOR VARYING;


CALL SCHEMANAME.SPNAME();

ASSOCIATE RESULT SET LOCATOR (loc_cursor) WITH PROCEDURE SCHEMANAME.SPNAME; 
ALLOCATE cur CURSOR FOR RESULT SET loc_cursor;


CLOSE cur;

END P1

Upvotes: 1

Views: 926

Answers (1)

mao
mao

Reputation: 12267

This is covered in the documentation.

The key details to learn about are locators, associate result set locators and then allocate ... cursor for result set. After that is done, your calling procedure can treat the cursor just like any other (fetching rows , close etc).

So if your nested procedures returns 1 result set, your calling sproc will declare a locator, associate the result-set locator with your called-procedure, then allocate a cursor to consume the result-set. Study the documentation for each of the involved statements.

Apart from the above, if you will always filter the result-set, why not make a procedure that performs the filtering at source (to avoid slow double filtering)? You can also have a procedure create a session-table and have callers access that session-table. Or use a table-function to return exactly what is needed?

Upvotes: 1

Related Questions