dynamic db2 query in stored procedure

Question

Hi i had a stored procedure as below,

Create  PROCEDURE my_car(
in diesel integer,
out milege integer)
P1: BEGIN

SET START_query = 'SELECT*';
SET FROM_CLAUSE = ' FROM car, diesel';
SET WHERE_CLAUSE = ' WHERE car.c1 = diesel.c2';

IF (diesel =0) THEN            
                SET WHERE_CLAUSE = +WHERE_CLAUSE+'AND car.diesel=0' ;
                ELSE
                SET WHERE_CLAUSE = +WHERE_CLAUSE+' AND car.diesel='+diesel ; 
               END IF;
END P1

But its throwing error as below.

DB2 SQL Error: SQLCODE=-402, SQLSTATE=42819, SQLERRMC=+, DRIVER=3.52.90

and if we use || instead of + ie.,

SET WHERE_CLAUSE = WHERE_CLAUSE || 'AND car.diesel=0' ;

then its throwing error as below,

DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=||;FUNCTION, DRIVER=3.52.90

please help on this.

Answer 1

In DB2, || rather than + is used for string concatenation.

Also, diesel is a character string and you are comparing it to zero. If you want diesel to be a 0 or 1 value, you should set its type to integer. If it needs to be a string, you should compare it to '' or '0' or whatever makes sense.

I'm not sure what you meant by +V_WHERE_CLAUSE+, but your code should look something like this:

IF (diesel = '0') THEN            
    SET WHERE_CLAUSE = WHERE_CLAUSE || 'AND car.diesel=0';
ELSE
    SET WHERE_CLAUSE = WHERE_CLAUSE || ' AND car.diesel=' || diesel; 
END IF;

Or maybe just this:

SET WHERE_CLAUSE = WHERE_CLAUSE || ' AND car.diesel=' || diesel;