...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.
Well, not quite true. First, as you probably already know, you can interact with TVF's with WHERE, GROUP BY, ORDER BY, etc.
Hi Jeff. I believe we are saying the same thing here. My point was expressing a preference for interacting with TVFs over Stored Procs. Maybe it would have been clearer if I said CLR Stored Procs. The point of what I wrote was that I feel many / most people are aware that CLR Stored Procs can send each row back as it is created but not everyone is aware of how to do that via TVFs.
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).
Second, you can use the output of a Stored Procedure rather directly using OPENROWSET:
Thank you for this information. I was not aware of using OPENROWSET like this so it is definitely good to know. Although personally, using a TVF seems like a cleaner / less clunky interface. Again, the point is that if one is already doing something in CLR, it is good to have the option of creating a Function over a Procedure.
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.
I generally agree with this statement and was not advocating that people use SQLCLR simply for the sake of using it or in doing something that is natively available in T-SQL. But, when a good use for SQLCLR is found it is good to not be forced into the Stored Procedure interface.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR