...but I wanted to create TVFs so that I could more easily interact with the output by doing WHERE conditions, GROUP BYs, ORDER BYs, etc. which are not possible with Stored Procedures unless you trap the output into a Table Variable or Temp Table (which I did not want to do).
Well, not quite true. First, as you probably already know, you can interact with TVF's with WHERE, GROUP BY, ORDER BY, etc. Second, you can use the output of a Stored Procedure rather directly using OPENROWSET:
SELECT *
FROM OPENROWSET('SQLOLEDB',
'server=(local);trusted_connection=yes',
'set fmtonly off exec sp_who2')
WHERE SPID >= 50
ORDER BY Status ASC, CPUTime DESC
Note that on SQL Server 2005 and up, you must enable 'Ad Hoc Distributed Queries' either through sp_Configure or the "Surface Area Configuration". If necessary, that functionality can be enabled and disabled on the fly in the code when a user with the correct level of permissions uses the code while it rejects those that do not.
I was quite interested to see what wonderful things could be done that either: a) couldn't be done in regular T-SQL or b) would at least be more efficient than being done in T-SQL.
I'm not picking on the author here... it's a general statement that I bring up to anyone who may be considering the use of CLRS... Don't use the excuse of a lack of knowledge of T-SQL functionality as a justification for CLR usage. You'll frequently pay (yes, there are exceptions) for such a mistake in the form of performance and resource usage. There are very few things that can't actually be done in T-SQL and there are very few things where a CLR will actually win the performance foot race if the T-SQL is properly written for performance. Even when T-SQL does lose the performance footrace, it's many times so close to the performance of CLR's that it's just not worth the time and effort to maintain a separate code base outside of T-SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.