Kevin, while well understanding that the client just wants it to work, I'm going to play devil's advocate on this and argue who's got the "procedural" mindset.
If we were talking about a relational database (vs a SQL database), there would no distinction between getting the needed values from a CTE or a "table", or a "view". In all cases, you're just asking for the variable with the values of your query.
Procedural programmers actually don't expect to get the value assigned at "declaration", but they do expect variables to hold onto values. This is reasonable.
More to the point, though, the programmer shouldn't have to think about the mechanism anyway. The programmer shouldn't be aware of a difference between a CTE or a "table" or a "view" - that's the whole point to relational databases.
The vendors however have done their best to make it look like an ISAM system, and now the so-called "object oriented" crowd has demoted it to a "persistence store" - even to the point of undoing most of the advantages of relational databases.
Solving your client's issue means dealing with the system as it exists, and you did that, and got them going. But I think you might be a little quick to attribute the flaw to "procedural thinking". It's in there, of course - but SQL and the database vendors don't make it easy to approach this right.
That's just arguing from the other side - your points are valid and well taken and it's an interesting real-life article.