How to display 2 select statement output through stored procedure in db2 under if condition

Question

Based on OutPutType=1, I need to display 2 select statement output. In the below code I am getting following error: (Please note In actual I have 2 different dataset in 2 select statement but here for reference I have written one select for time and another statement used for date)

SQL Error [42601]: An unexpected token "" was found following "". Expected tokens may include: "OM sysibm.sysdummy1'".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.21.29

CREATE OR REPLACE
PROCEDURE Test.Test1 ( 
IN OutPutType SMALLINT)

DYNAMIC RESULT SETS 2 LANGUAGE SQL SPECIFIC test1
BEGIN
DECLARE v_cursor_text VARCHAR(5000);
DECLARE v_cursor_text1 VARCHAR(5000);

DECLARE C1 CURSOR WITH RETURN FOR Statement1;
DECLARE C2 CURSOR WITH RETURN FOR Statement2;

 IF OutPutType = 1 THEN

 SET
v_cursor_text = ' SELECT CURRENT_TIME   FROM sysibm.sysdummy1';
        
v_cursor_text1 = 'SELECT CURRENT_DATE   FROM sysibm.sysdummy1';
        
END IF;

 PREPARE Statement1
FROM
v_cursor_text;
 PREPARE Statement2
FROM
v_cursor_text1;

OPEN c1;
OPEN C2;
END

If your procedure can have TWO distinct result-sets then you must use `DYNAMIC RESULT SETS 2` when creating the procedure, and in the case that you only have 1 result-set then the second result-set must yield no rows. This might be considered a poor design. — mao, Oct 14 '20 at 13:17
@mao I tried doing with dynamic result set 2 but still got the same error — Robin, Oct 14 '20 at 13:20
your syntax error results from your example procedure missing a `SET` before `c_cursor_text1`. Fix your syntax. — mao, Oct 14 '20 at 13:26

score 0 · Answer 1 · answered Oct 13 '20 at 22:50

You don't show two select statements for "OutputType=2"...

The only select statements you set are for "OutputType=1"

also, you have DYNAMIC RESULT SETS 1 but open both cursors...so you should have DYNAMIC RESULT SETS 2

Neither of the select statements you show need to be dynamic, you could simply use a

DECLARE C1 cursor for
  SELECT CURRENT_TIME FROM sysibm.sysdummy1;

Hopefully you aren't actually intending to get the date and time this way...

Lastly you should tag the question with the actual Db2 platform, not both db2-400 and db2-luw.

Hi Charles, those 2 select statements are dummy in nature... I have 2 different data set which I need to display for Output Type=1... — Robin, Oct 13 '20 at 22:59

score 0 · Answer 2 · answered Oct 14 '20 at 02:29

0

Use only one cursor. Build SQL statement based on input parameter.

DECLARE C1 CURSOR WITH RETURN FOR Statement1;
    
IF OutPutType = 1 THEN
 SET v_cursor_text = ' SELECT CURRENT_TIME   FROM sysibm.sysdummy1';
else       
 set v_cursor_text  = 'SELECT CURRENT_DATE   FROM sysibm.sysdummy1';
END IF;

PREPARE Statement1
FROM
v_cursor_text ;

Open c1;

answered Oct 14 '20 at 02:29

Satya

583
1
4
6

hi @satya, when output type is 1 then I need to show both select statement output. One is detail output and other is summary output. So in this case I cannot use if else within select statement. – Robin Oct 14 '20 at 12:59

score 0 · Accepted Answer · answered Oct 14 '20 at 13:38

Your syntax error results from a missing SET verb before v_cursor_text1.

To get the procedure to compile, your sample should read:

--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE Test.Test1 ( IN OutPutType SMALLINT)
DYNAMIC RESULT SETS 2 
LANGUAGE SQL SPECIFIC test1
BEGIN
    DECLARE v_cursor_text VARCHAR(5000);
    DECLARE v_cursor_text1 VARCHAR(5000);

    DECLARE C1 CURSOR WITH RETURN FOR Statement1;
    DECLARE C2 CURSOR WITH RETURN FOR Statement2;

    IF OutPutType = 1 THEN
        SET v_cursor_text = ' SELECT CURRENT_TIME   FROM sysibm.sysdummy1';
        set v_cursor_text1 = 'SELECT CURRENT_DATE   FROM sysibm.sysdummy1';
    END IF;

    PREPARE Statement1 FROM v_cursor_text;
    PREPARE Statement2 FROM v_cursor_text1;
    OPEN c1;
    OPEN C2;
END@

How to display 2 select statement output through stored procedure in db2 under if condition

3 Answers3