Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    2

    Unanswered: stored procedure array parameter

    Is it possible to pass an array as an in parameter to an SQL stored procedure? If not, is there some other type of collection which can be passed in?

    Thanks

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    I could be wrong but I don't believe there is(?).

    You can get round it though by using dynamic SQL in your SPs.

    CREATE PROCEDURE SP_TEST
    (
    IN P_ARRAY VARCHAR(20)
    )
    LANGUAGE SQL

    P1: BEGIN
    DECLARE V_DYNAMIC VARCHAR(100);
    DECLARE V_SQL VARCHAR(100);

    DECLARE C_TEST CURSOR WITH RETURN FOR V_DYNAMIC;

    SET V_SQL = 'SELECT * FROM TMP_TEST WHERE COL IN (' || P_ARRAY || ')';

    PREPARE V_DYNAMIC FROM V_SQL;

    OPEN C_TEST;
    END P1
    @

    CALL SP_TEST ('<YOUR_ARRAY_AS_COMMA_DELIMITED_STRING>')
    @

    The above has its limitations obviously but it might help you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •