CODEWITHSUNDEEP
db2db2-luwdatabase-cursor
Pavan Kathi
Pavan Kathi

Reputation: 51

Return result set using Weakly-typed cursor Variable

CREATE or replace PROCEDURE return_result_set ( )
    LANGUAGE SQL
    SPECIFIC return_result_set                   
DYNAMIC RESULT SETS 1
rrs: BEGIN
    
    DECLARE rs_cur CURSOR WITH RETURN
        FOR SELECT *
            FROM dummytable;
    OPEN rs_cur;                                  
                                            
END rrs

I can return result set using cursor by using above stored procedure, but I want to use cursor variable (Weakly-typed) in my stored procedure as the select query and table are going to vary based on a condition. Sample code here seems to be for different use case..

How to return a result set using a Cursor Variable?

Upvotes: 0

Views: 235

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12339

Try this as is:

--#SET TERMINATOR @

SET SERVEROUTPUT ON@

BEGIN
  DECLARE V_C1 CURSOR;
  DECLARE V_I INT;
  
  DECLARE PROCEDURE L_PROC(OUT LP_C1 CURSOR) 
    BEGIN 
      -- Dynamic cursor
      --DECLARE V_STMT VARCHAR(128) DEFAULT 'SELECT * FROM (VALUES 1) T(I)';
      --PREPARE V_S1 FROM V_STMT;
      --SET LP_C1 = CURSOR FOR V_S1;

      -- Static cursor
      SET LP_C1 = CURSOR FOR SELECT * FROM (VALUES 1) T(I);

      OPEN LP_C1;
    END;
 
  CALL L_PROC(V_C1);
  FETCH V_C1 INTO V_I;
  CALL DBMS_OUTPUT.PUT_LINE('I: ' || V_I);
  CLOSE V_C1;
END@

SET SERVEROUTPUT OFF@

Upvotes: 1

Related Questions