To me the biggest drawback to using SPs is it makes it very difficult to give the user a UI where they can do whatever query they want. Power to the user is what PCs were originally designed for. It doesn't always have to be that way. Maybe you can give some specifics from a real-world example, where you feel the UI limits the user?
I've seen some pretty elaborate UIs that allow the user to customize and tailor a result set, where SPs were running in the background.
Kevin