I have the following CASE statement eight times in a select statement (
rooms 1 to 8 :) ), and it check if a room is booked, or a member of
staff is busy for a time slot. It works fine, and response is acceptable
( this is a PHP application ) but I would like to tidy things up.

CASE
WHEN ( ( SELECT STAFF_ID FROM APPOINTMENT
WHERE APPOINT_DATE = :dateam
AND APPOINT_TIME = B.ATIME AND ROOM = 1 ) IS NOT NULL )
THEN ( SELECT SURNAME || ',' || FORENAME FROM STAFF
WHERE STAFF_ID = ( SELECT STAFF_ID FROM APPOINTMENT WHERE

APPOINT_DATE = :dateam
AND APPOINT_TIME = B.ATIME AND ROOM = 1 ) )
WHEN ( ( SELECT STAFF_ID FROM APPOINTMENT
WHERE APPOINT_DATE = :dateam
AND APPOINT_TIME = B.ATIME AND STAFF_ID = $staff )
IS NOT NULL )
THEN 'booked'
ELSE 'book button'
END AS BK1

What are peoples thoughts as to making this into a stored procedure -
can I actually do this in a stored procedure, or am I better of leaving
it as it is.

Some applications may need more rooms, and while the initial timeslot
plan is only 12 entries, a switch to 50+ is possibly required, so I need
to be a bit more flexible.

Any thoughts, or should I just stick with the working version ;)

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services