But for one thing! The whole idea of SP being compiled versus raw SQL may have been true in SQL 6.5, but is now a myth. In fact, now it's the SP that uses EXEC to execute contrived SQL that does not use the query cache.

Precisely! I'm wondering when it became less fashionable to try to use parameterized queries wherever possible to gain benefits from the cache, and because they prevent SQL injection. IMHO the SQL injection issue is even more important with SPs because concatenated SQL inside a privileged SP may allow malicious attacks from which there is no defense. But I'm also reminded of an old argument (still preserved on the wiki) that boiled down to "SP is faster than SPT because if it isn't, you can buy a bigger server." ;-)