Results 1 to 2 of 2
  1. #1
    Join Date
    May 2007
    Posts
    11

    Red face Unanswered: Will this DB2 SP work?

    Will this DB2 SP work?

    I am specially doubtful about the line marked in red

    CREATE PROCEDURE WPWDB.WPO_GET_USER_ACCESS_TYPE_PP (
    IN IN_LOB VARCHAR(20),
    IN IN_PLAN_CODE VARCHAR(20))
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    DECLARE conditionString varchar(200);
    DECLARE dynamicString varchar(200);
    DECLARE sqlStatement1 varchar(2000);
    DECLARE selectCursor CURSOR WITH RETURN FOR sqlPrepare;
    IF IN_LOB IS NOT NULL THEN
    SET dynamicString = 'PG.PLAN_CODE =' CONCAT '''' CONCAT IN_PLAN_CODE CONCAT '''';
    SET conditionString='AND (PG.LOB IS NULL OR PG.LOB='')'CONCAT dynamicString;
    ELSE
    SET conditionString = 'PG.PLAN_CODE =' CONCAT '''' CONCAT IN_PLAN_CODE CONCAT '''' CONCAT 'AND PG.LOB =' CONCAT '''' CONCAT LTRIM(RTRIM(IN_LOB)) CONCAT '''' ;
    END IF;
    SET sqlStatement1 = 'SELECT PG.ACCESS_INDICATOR from WPWDB.PLAN_GROUP PG where 'CONCAT conditionString;
    PREPARE sqlPrepare from sqlStatement1;
    OPEN selectCursor;
    END P1


    Nivin
    Last edited by nivin; 10-05-07 at 00:18.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by nivin
    Will this DB2 SP work?

    I am specially doubtful about the line marked in red
    You don't do anything special (just basic string concatenation). However, the SQL statement that you create is syntactically incorrect if IN_LOB is NULL. Other than that, it should work. What you could do is to modify the procedure (for testing purposes only) and return the generated statement as OUT parameter (remove the PREPARE/OPEN statements as well). Then you can verify the SQL statements inside the procedure.

    DECLARE selectCursor CURSOR WITH RETURN FOR sqlPrepare;
    I'm not sure if this DECLARE statement must come after the PREPARE. In case DB2 complains about that, move it further down and nest it into a BEGIN ... END block (because it has to be at the beginning of such a block.

    SET dynamicString = 'PG.PLAN_CODE =' CONCAT '''' CONCAT IN_PLAN_CODE CONCAT '''';
    You can simplify this a bit to:
    Code:
    SET dynamicString = 'PG.PLAN_CODE = ''' CONCAT IN_PLAN_CODE  CONCAT '''';
    You can also use || instead of CONCAT. Both are synonyms. It depends on personal preferences which one you use. I like || better because it cannot be confused as being part of the string to be build.

    Another question here is: you test whether IN_LOB is NULL. But what happens in IN_PLAN_CODE is NULL? You should test for that as well!

    SET conditionString='AND (PG.LOB IS NULL OR PG.LOB='')'CONCAT dynamicString;[/COLOR]
    Here you have a problem: the first token after the keyword WHERE will be "AND". That is not allowed, of course.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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