There are a great many uses for stored procedures. They can eliminate round trips, and they can encapsulate logic in the database. It is a pity that MySQL stored procedures are so limited that hey do not even encapsulate the full standard, and require difficult constructs by not implementing "for" loops, and other language conveniences. But they are very powerful. I am probably the only person outside of Oracle to publish open source tools that rely on them heavily, because the logic they employ belongs in the database (Flexviews and PS_history being the primary two). As an example from Oracle, the sys schema uses stored functions to great effect.
Just because something is clunky, it does not make it worthless, it simply means that more effort has to be put in to get the desired result, but the effort can very well be worth it.