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.
Hello Phil, 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. I wanted to pass a result set to the reporting module, and found myself unable to do it without resort to a procedure. I ended up creating temporary tables, looping through the pupils doing INSERTS to them, SELECTing from these temp tables JOINed and then DROPping the temporary tables. Another recent tricky one, connected with the Attendance Monitor component I wrote (screenshot at my site www.openi.org) was where I wanted to process temporary tables, whose size was not known in advance. I ended up using two nested cursors. Ugh! Now I know some of this may be down to my inability to think in set terms as opposed to procedures, but probably not entirely so. I have of course tried hard to model all this stuff in a proper normalized way. regards Richard richard@tortoise.demon.co.uk