One straightforward example would be feeding some kind of dashboard. Imagine a time-consuming query using a complex series of CTEs to build up or filter or redu

One straightforward example would be feeding some kind of dashboard. Imagine a time-consuming query using a complex series of CTEs to build up or filter or reduce a very large set of rows, then running a bunch of "final" selects to transform and carve up that set in different ways to feed a bunch of tables and graphs. Obviously this could be done in various ways, but making one request to a procedure and receiving one response with multiple result sets can be a very pragmatic (and performant) approach.