Topic
  • 4 replies
  • Latest Post - ‏2012-06-29T21:26:37Z by SteveDBros
SteveDBros
SteveDBros
3 Posts

Pinned topic How to pass a WHERE IN list to a stored procedure

‏2012-06-28T20:59:53Z |
Greetings! Does anyone know how to pass a list of values to a stored procedure to be used in a WHERE IN statement? You can see below where I took a guess and passed a comma-delimited character string, but that doesn't work. It runs okay but does not select anything. I assume it's treating the "list" as a single value. Thanks!
DROP PROCEDURE WM300RTMD.et018qo_02;

CREATE PROCEDURE WM300RTMD.et018qo_02 (
in i_wdshft char(100),
in i_wdprty numeric(5,2)
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE C1 CURSOR FOR
select * from WM300RTBD.WDWAVE00
where wdshft in (i_wdshft) and wdprty=i_wdprty
fetch first 10 rows only
;
OPEN C1;
RETURN;
END
;

CALL WM300RTMD.et018qo_02 ('02,03', .09);
Updated on 2012-06-29T21:26:37Z at 2012-06-29T21:26:37Z by SteveDBros
  • krmilligan
    krmilligan
    450 Posts

    Re: How to pass a WHERE IN list to a stored procedure

    ‏2012-06-29T14:17:48Z  
    What release level are you on? The DB2 for i 7.1 release supports an SQL array type. Without the array support, you'd have to substring the parameter into different variables.
  • SteveDBros
    SteveDBros
    3 Posts

    Re: How to pass a WHERE IN list to a stored procedure

    ‏2012-06-29T18:00:16Z  
    What release level are you on? The DB2 for i 7.1 release supports an SQL array type. Without the array support, you'd have to substring the parameter into different variables.
    Thanks very much for the reply. Alas, according to System I Navigator: i5/OS Version: Version 6 Release 1 Modification 0.

    Now, what you say makes sense. In order for the IN list to be a variable it would have to be an array (or some sort of collection). Forgetting passing parameters for a moment, are you implying that my version of DB2 simply does not support using a variable in the IN list (other than a one value character string)? That wouldn't be very nice at all!
  • krmilligan
    krmilligan
    450 Posts

    Re: How to pass a WHERE IN list to a stored procedure

    ‏2012-06-29T20:19:20Z  
    Thanks very much for the reply. Alas, according to System I Navigator: i5/OS Version: Version 6 Release 1 Modification 0.

    Now, what you say makes sense. In order for the IN list to be a variable it would have to be an array (or some sort of collection). Forgetting passing parameters for a moment, are you implying that my version of DB2 simply does not support using a variable in the IN list (other than a one value character string)? That wouldn't be very nice at all!
    The IN clause can reference a variable, but a variable can only contain a single value. So the IN clause would need to reference multiple variables.

    If you don't mind using Dynamic SQL, this can be solved on 6.1 with following logic:

    BEGIN
    DECLARE v1 VARCHAR(256);
    DECLARE Cur1 CURSOR FOR stmt1;

    SET v1 = 'select * from WM300RTBD.WDWAVE00
    where wdshft in (' || i_wdshft || ')
    and wdprty = ?
    fetch first 10 rows only';

    PREPARE stmt1 FROM v1;
    OPEN Cur1 USING i_wdprty ;
    END;
  • SteveDBros
    SteveDBros
    3 Posts

    Re: How to pass a WHERE IN list to a stored procedure

    ‏2012-06-29T21:26:37Z  
    The IN clause can reference a variable, but a variable can only contain a single value. So the IN clause would need to reference multiple variables.

    If you don't mind using Dynamic SQL, this can be solved on 6.1 with following logic:

    BEGIN
    DECLARE v1 VARCHAR(256);
    DECLARE Cur1 CURSOR FOR stmt1;

    SET v1 = 'select * from WM300RTBD.WDWAVE00
    where wdshft in (' || i_wdshft || ')
    and wdprty = ?
    fetch first 10 rows only';

    PREPARE stmt1 FROM v1;
    OPEN Cur1 USING i_wdprty ;
    END;
    Thank you! You're better than google. I couldn't find much at all on this subject out there.