From older ones like COALESCE to newer ones like Common Table Expressions, there's more and more you can do in stored procs.But that's an entirely new point... and you say you're "done" while it's still expressed in generalities.
I recall that CTE have application if you're doing munging or recursive selects (which I need to do a lot) at the server. But don't you have to use it immediately after creation? Persistence is one of the advantages I raised for an auto-spanning VFP cursor.
I have an idea- pick one of your SQL Server MVP pals and invite them here to post latest thinking about SP and data munging. IMHO there would be heaps of interest.
"... They ne'er cared for us
yet: suffer us to famish, and their store-houses
crammed with grain; make edicts for usury, to
support usurers; repeal daily any wholesome act
established against the rich, and provide more
piercing statutes daily, to chain up and restrain
the poor. If the wars eat us not up, they will; and
there's all the love they bear us."
-- Shakespeare: Coriolanus, Act 1, scene 1
