Had school trip to Sun Centre at Rhyl once. Was the hot attraction 20-odd years ago as it had a, gasp, wave machine, but not so much fun for a non-swimmer like myself. I hurt my arse coming off the bottom of the baby's elephant slide in the shallow end.
The Alton Towers water park thing is meant to be great fun if you're making a special trip. On a smaller scale, there's an outdoor water park / pool thing at Scarborough that looked quite fun, as we peered through the railings last year.