Why use CLR procedures for simple queries?

Allow me to preface this by saying I'm really excited about writing stored procedures, etc. in C#!! Now...on to my question. When an application needs a simple result set (i.e., SELECT....), why use C# to write this In the samples I've seen, the developer ends up writing the select statement anyway in the CLR hosted stored procedure. What would be the benefit You end up writing more code just to write the same query. Now, executing complex logic is another story. I see HUGE benefits to hosting classes on the data server. Anyway, what is Microsoft's answer to this question Is it recommended that one still write simple statements in T-SQL and leave the complex stuff to CLR code Thanks in advance for your advice!

Answer this question

Why use CLR procedures for simple queries?

dotNetFan
Another consideration is that the business logic is not as exposed as with classic stored procedures if you are deploying the solution to a third-party server.
AirBear
Another way to arrive to the same conclusion:

You cannot access data directly from within SQLCLR code--instead, you must use ADO.NET to execute T-SQL on the server.

So, for simple CRUD operations, it's more efficient to bypass the whole ADO.NET part, and just write T-SQL procedures directly.

You can also consider SQLCLR procedures when you want to move business logic onto the database tier in order to eliminate the need to transfer large amounts of data across the network for processing.
mnoon
Thanks for the input. It makes perfect sense!
Joe C...
For simple select statements you should NOT use the CLR. CLR is useful for complex, processing intensive calculations, when you need access to outside resources, when you do stuff that is really hard to do with T-SQL etc. Basically: data manipulation - use T-SQL, calculations etc - CLR.

Niels
Why use CLR procedures for simple queries?

Answer this question