Useful SQL tip for handling optional parameters

Folks:

I just had a situation where I needed to handle an optional parameter on a procedure, where the parameter was being used for record selection in a SQL statement.

I didn't want to deal with multiple SQL statements based on if the parameter was passed or not.

So here's routine I put together ...

dcl-proc myproc export;
dcl-pi *n;
parm1 char (10) const;
parm2 char (10) const options(*nopass);
end-pi;

dcl-s value2 like(parm2);

if %parms > 1;
value2 = parm2;
else;
value2 = '*ALL';
endif;

exec sql
delete from FILE
where FIELD1 = :parm1 and
(FIELD2 = :value2 or :value2 = '*ALL');

end-proc;

I wasn't sure if the ":hostvalue = 'constant'" criteria was going to work, but it does appear to work fine.

david