On Sat, 5 May 2001 richard@tortoise.demon.co.uk wrote:
I'd be interested to see the category of problem that would be difficult without stored procedures (not that I'm an expert on them, but I've never yet been in a situation where I haven't been able to do what I need for lack of them. I do not claim to be an SQL expert either, there are situations, I'm sure where I've written procedural code where an SQL set-based expression would have done the job. However, consider this: Pupils may be entitled to free school meals (fsm). A local authority will provide the school with a list of pupils who are thus entitled from say start_date to end_date. The system keeps a record of periods of entitlement for each pupil so that should some query be requested, involving say total_number_of_days_of_fsm_entitlement and GCSE_outcomes then it could be done. The system produces printed output each week, namely a grid covering a (say) two week period, with days across the top and pupil names down the side. In each cell is indicated whether the pupil is entitled to fsm or not. This printout is used at the entrance to the dining area. When a pupils period of fsm entitlement expires, the school allows a (configurable, looked up from a table of user defined settings) number of days grace before refusing to provide a meal. The printout indicates such grace days in the appropriate cell on the sheet. However, the system also has tables recording periods of school holiday, and these do not count against grace days. So, the report has to generate the grid and fill in each cell with: Entitled, Not entitled, Holiday or Grace.
As a (fairly rough) first approximation, how about: Create a function, called e.g. fsm_status, that takes as inputs the pupil ID and the date, and returns the appropriate status ("Entitled", etc.). Exactly how you do this depends on how the data about school meal periods, holidays etc. is stored, and also depends on whether the start_date and end_date in the list from the local authority are common to all pupils or not (I presume that they are). Create a query that looks something like: SELECT pupils.name, fsm_status(pupils.id, today), fsm_status(pupils.id, today+1), ... (optional) Create a view, called e.g. fsm_report, based on this query. Involves a stored function, but not temporary tables, and the end result is that you can just SELECT * FROM fsm_report and feed it straight to the report generator. Michael