When using SQLCLR?

Always make your natural choice for data operations "T-SQL" to get benefits of ease of use and deploy and query plans and caching query plans,..etc unless you face the following issues:

•  Complex and heavily operations
• Pattern matching
• Deals with data out of Sql server
• Needs to write extended stored procedure by using C++ and COM.

Why Procedural and recursive code works much better in CLR?

CLR code and higher-level programming languages such as C# and VB .NET have offered better support for procedural and recursive code. This is because such languages are designed from the ground up to support per method call-based stack frames and activation records, and they do not suffer from limits such as a maximum call depth of 32 in recursive operations like in T-SQL.

So there are times or reasons to use SQLCLR or T-SQL and basically for example writing outer join using T-SQL in stored procedure easier than CLR stored procedure and better performance and it does not need to host additional resources like SQLCLR engine or more step to get the result.

Shortly

"The use of CLR within SQL Server is an important aspect of making SQL Server more extensible, allowing developers to do more than what is possible with just the T-SQL language and its system functions"

The following types of objects can be created using SQLCLR:

1. Stored procedures
2.  Scalar or Table-valued UDFs
3. Triggers (DML, DDL, and logon triggers)
4. User-defined aggregates
5. User-defined types (UDTs)

Note: user-defined aggregates and UDTs, can be created only using SQLCLR; they cannot be created using T-SQL.