Menu
My current view is no, prefer Transact SQL stored procedures because they are a lighter weight and (possibly) higher performing option, while CLR procedures allow developers to get up to all sorts of mischief.
However recently I have needed to debug some very poorly written TSQL stored procs. As usual I found many of the problems due to the original developer developer having no real TSQL experience, they were ASP.NET / C# focused.
So, using CLR procedures would firstly provide a much more familiar toolset to this type of developer, and secondly, the debugging and testing facilities are more powerful (ie Visual Studio instead of SQL Management Studio).
I'd be very interested in hearing your experience as it's seems it is not a simple choice.
613
Stored procedures are a collection of Transact-SQL statements stored within the database. They are used to encapsulate oft-used queries, such as conditional statements, loops, and other powerful programming features. Stored procedures are similar to functions in high-level programming languages.
Stored procedures are difficult to unit test. With an ORM, you can mock your database code so as to be able to test your business logic quickly. With stored procedures, you have to rebuild an entire test database from scratch. Stored procedures offer no performance advantage whatsoever.
What are the CLR Stored procedures. The CLR is a common language runtime, and the SQL Server stored procedures are a collection of the SQL Queries and the command logic. The stored procedures are compiled and stored in the database. The CLR stored procedures are the combination of the CLR and stored procedure.
There are places for both well-written, well-thought-out T-SQL and CLR. If some function is not called frequently and if it required extended procedures in SQL Server 2000, CLR may be an option. Also running things like calculation right next to the data may be appealing. But solving bad programmers by throwing in new technology sounds like a bad idea.
141
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
