What is a good example of when you'd want to do that? I've been doing database work on the 4 majors for about 15 years and this is the first I've ever heard of somebody wanting to return two totally different result sets from a single procedure.
I have no issue with putting logic in your database. In many cases it's the ideal solution...but your example is something I've never even heard mentioned in 15 years much less advocated for or used as criticism for not being available.
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.
One place I worked used a disconnected data set model. The idea was a single stored procedure returned multiple result sets all relating to a single business entity; specifically, an insurance quote, along with all the drivers, vehicles, accidents, convictions, etc. The entity was locked by inserting a row in a table.
Thereafter, the application didn't communicate with the database until it was time to save the record. The disconnected data set was small enough to store in shared session store, or even in a cookie (encrypted, naturally).
This architecture had a number of interesting knock-on effects. The entire state of a client's conversation with the server was tiny and entirely encapsulated by this disconnected data set, so you could record and play back each request to recreate a bug.
It didn't have to use multiple result sets from the stored procedure, but it did save a bunch of round trips.
I won't comment on anything else because I don't know the case, but you should never put user data in cookies. Even with encryption you are exposing it unnecessarily, not to mention you have to double check data integrity... Why? Cookies are not meant for this.
One query, multiple result sets--so you can get a customer and the customer's orders in one shot without any join awkwardness. It's part of the jdbc spec and somewhat common in mssql land.
Or when you can do common subexpression elimination, where you can compute something such as a temporary table, then use that to produce result R, then use it to produce result S.
I have no issue with putting logic in your database. In many cases it's the ideal solution...but your example is something I've never even heard mentioned in 15 years much less advocated for or used as criticism for not being available.