I've actually been implementing this technique for quite a while in certain situations where the flexibility is necessary to create dynamic SQL.  You also have the ability to create temporary stored procedures from the dynamic code.  Another benefit of this approach is that the code is database independent (a big drawback of SPs is that they're tied to a particular database) and I can use the code in a multitude of scenarios/databases and keep only 1 copy (1 copy?  Oxymoron?  Oh well).  There are so many factors that are necessary to keep the supposed speed advantage of SPs; indexes in optimal health, statistics constantly updated (especially if lots of updating), and keeping SQL Server from dynamically recompiling them anyway!  We have seen noperformance advantages for SPs in most of our tests/usage scenarios.