CASE

From SQL PL Guide for DB2

DB2 is supporting two forms of the CASE statement: the simple-case-statement and the searched-case-statement.

It's important to know that the CASE statement is different to the CASE expression which will be used in SELECT statements.

Simple Case Statement

Example:

create procedure number_of_objects (in obj_type varchar(1),out num_obj integer)
language sql
begin
 case obj_type
   when 'T' then select count(*) into num_obj from syscat.tables where type='T';
   when 'V' then select count(*) into num_obj from syscat.tables where type='V';
   when 'I' then select count(*) into num_obj from syscat.indexes;
   when 'P' then select count(*) into num_obj from syscat.procedures;
   when 'F' then select count(*) into num_obj from syscat.functions;
 end case;
end!

Searched Case Statement

Example: (similar to the simple case example)

create procedure number_of_objects2 (in obj_type varchar(1),out num_obj integer)
language sql
begin
 case
   when obj_type = 'T' then select count(*) into num_obj from syscat.tables where type='T';
   when obj_type = 'V' then select count(*) into num_obj from syscat.tables where type='V';
   when obj_type = 'I' then select count(*) into num_obj from syscat.indexes;
   when obj_type = 'P' then select count(*) into num_obj from syscat.procedures;
   when obj_type = 'F' then select count(*) into num_obj from syscat.functions;
 end case;
end!

---

Link to the DB2 Information Center: DB2 9 DB2 9.5