CODEWITHSUNDEEP
sqldb2
umesh dhakar
umesh dhakar

Reputation: 11

How to use dynamic AND or OR operator in where clause in DB2?

DECLARE FILTER_DATA CURSOR WITH RETURN FOR
SELECT C_ID, C_NAME 
FROM DB2ADMIN.COURSES
WHERE C_ID = PARM_ID AND AND C_NAME = PARM_NAME

A procedure contains this cursor and the cursor have AND operator in WHERE clause, but I want to make this operator dynamic based on procedure parameter.

So that the cursor return resultset on the basis of either AND or OR operator.

Thanks in advance!

Upvotes: 0

Views: 251

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12314

Use dynamic sql.

begin
  ...  
  DECLARE FILTER_DATA CURSOR WITH RETURN FOR s1;

  CASE PARM_FLAG
    WHEN 1 THEN
      PREPARE S1 FROM 'SELECT C_ID, C_NAME FROM DB2ADMIN.COURSES WHERE C_ID = ? AND C_NAME = ?';
    ELSE
      PREPARE S1 FROM 'SELECT C_ID, C_NAME FROM DB2ADMIN.COURSES WHERE C_ID = ? OR C_NAME = ?';
  END CASE;

  OPEN FILTER_DATA USING PARM_ID, PARM_NAME;
end@

If you expect NULL parameter input, you may do something like this for each statement:

PREPARE S1 FROM 'SELECT C_ID, C_NAME FROM DB2ADMIN.COURSES WHERE '
||case when PARM_ID is null then 'cast(? as int) is null' else 'C_ID = ?' end
||' AND '
||case when PARM_NAME is null then 'cast(? as varchar(1)) is null' else 'C_NAME = ?' end;

Upvotes: 1

Erwin Smout
Erwin Smout

Reputation: 18408

Don't.

Prepare/use a distinct SQL statement for each possible case/predicate (each possible kind of where clause) and pick the one to use based on the user input you're getting.

Technically, it is acceptable to write stuff like

WHERE :PARM_ID IS NULL OR ID = :PARM_ID

or

WHERE (:CASE = 'OR' AND (C_1 OR C_2)) OR (:CASE = 'AND' AND (C_1 AND C_2))

but you stand very little chance that it will also be acceptable performance-wise. (You have a near guarantee of a full table scan for each single invocation.)

Upvotes: 1

Related Questions