Question: I'm using DB2 v9

I have a stored procedure with paramaters that can potentially be passed in as empty strings. Here somer pseudocode for what I'm trying to do:

```
WHERE myColumn.name =
     IF param1 = '' THEN
        **disregard this param, all column values are eligible**
     ELSE
        myColumn.name = param1;

```

Basically just ignore the param if it is an empty string. If not, apply it as a filter in the WHERE clause. Is this possible to do in DB2?

Answer: 
```
SELECT ...
FROM ...
WHERE  param1 = '' 
   OR  myColumn.name = param1
   ;

```
The [answer](https://stackoverflow.com/questions/10214340/db2-conditional-where-clause/10214393#10214393) provided by [wildplasser](https://stackoverflow.com/users/905902/wildplasser) is correct, but there are some additional considerations that may helpful to include.

First, it might be possible that the stored procedure input parameter contains a NULL instead of ''. Using the [COALESCE](http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000780.html) or [NULLIF](http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000833.html) functions will cover both a NULL and any amount of empty space:

```
SELECT ... FROM ... WHERE COALESCE( param1, '' ) = '' OR myColumn.name = param1 ;

```

When that type of search query is compiled into a stored procedure, it generally helps to enable REOPT ALWAYS on the statements inside the procedure. If you don't do this, the SQL statements inside your stored procedure will always use the same access plan, regardless of which input parameters are passed into the stored procedure at runtime. Allowing the optimizer to re-evaluate each statement in the procedure at runtime will provide a much better chance of exploiting the right index when the user searches on a particular column.

```
CALL SYSPROC.REBIND_ROUTINE_PACKAGE 
    ('P','YOURPROCSCHEMA.YOURPROCNAME','REOPT ALWAYS') ; 

```