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.