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

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.

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.

You can simplify this a bit to:
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!

Here you have a problem: the first token after the keyword WHERE will be "AND". That is not allowed, of course.