I have some stored procedures that I need to write against a nasty beast of an database. I need to loop through a table (application) and pull values out of other tables (some are aggerate / averages /etc values) using the application_id from the application table.
So far I have:
declare @id INT
declare app cursor for
SELECT application_id from application
OPEN app
FETCH NEXT FROM app
INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT a.NAME_LAST, a.NAME_FIRST, ca.status, (SELECT AVG(score) FROM reviews WHERE application_id = @id), (SELECT count(*) FROM reviews WHERE application_id = @id) FROM application a, committee_applications ca WHERE a.application_id = ca.application_id AND a.application_id = @id
FETCH NEXT FROM app INTO @id
END
CLOSE app
DEALLOCATE app
Which is giving me the results I want, but I'm sure there is a cleaner way of doing this, and I can't seem to make the mental jump today to do this correctly. Could someone point out a better way of doing this as this seems really ugly to me.
Also, it seems like I should be storing these values into a temp table then returning the full results instead of running the SELECT statement one by one.
Any suggestions would be greatly appreciated.
Thanks.