You can create CLR stored procedures in SQL Server. This article by Jignesh Trivedi provides some basic information on how to get the job done.
In SQL Server 2005 and later versions we are able to create CLR database objects, in other words functions, Stored Procedures, triggers and so on. Sometimes these CLR objects are faster than T-SQL. Generally a CLR database object can be used when we want to implement logic that is not possible with T-SQL or it can use more resources.
Advantages of CLR Stored Procedure
- CLR Stored Procedures are managed code so it ensures type safety, memory management and so on.
- It provides Object Oriented Programming capability hence it enables us to use encapsulation, polymorphism and inheritance.
- A CLR Stored Procedure can be written in C#, VB.NET or any other language supported by the .NET Framework.
- It is very useful when executing complex logic like intense string operation or string manipulations, cryptography, accessing a third-party library, accessing system resources and file management and so on.
Limitations of CLR Stored Procedure
- Deployment may be difficult in some scenarios.
- It is not convenient in all contexts, such as it should not be used to execute the simplest queries.