Post
Ask Question

How to enable CLR feature and execute CLR function

What is CLR:
 
The common language runtime (CLR) provides the execution environment for all .NET Framework code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.
 
When to use CLR integration in SQL Server
  1. If your procedure or function feature in complex logic.
  2. If you want to access class library which you develop using .Net programming language.
Steps to use CLR Integration in SQL Server
 
Step 1 Enable CLR in Sql Server
  1. EXEC sp_configure 'clr enabled', '1'  
Step 2 CLR re cofigure
  1. reconfigure  
Step 3 Enable trustworthy for unsafe permission on database
  1. ALTER DATABASE <Database_Name> SET TRUSTWORTHY ON;  
Step 4 Create Managed code (class library) in C# to show output with example.
  1. using System;  
  2. using System.Data.SqlTypes;  
  3. using Microsoft.SqlServer.Server;  
  4. namespace CLRLibraryFunction  
  5. {  
  6. public class UserDefinedFunctions  
  7. {  
  8. [SqlFunction(Name = "GetLongDate",  
  9. DataAccess = DataAccessKind.None)]  
  10. public static SqlString GetLongDate(SqlDateTime DateVal)  
  11. {  
  12. return DateVal.Value.ToLongDateString();  
  13. }  
  14. }  
  15. }  
Step 5 Create CLR assembly in SQL Server
  1. CREATE ASSEMBLY GetLongDateAssembly  
  2. FROM 'F:\CLRLibraryFunction.dll'WITH PERMISSION_SET = UNSAFE  
Step 6 Create CLR function in SQL Server
  1. CREATE FUNCTION GetLongDate(@d as DateTime)  
  2. RETURNS nVarchar(50)  
  3. AS EXTERNAL NAME GetLongDateAssembly.[CLRLibraryFunction.UserDefinedFunctions].GetLongDate  
Step 7 Execute Function with employee table
  1. SELECT dbo.GetLongDate(<date>)  
That's all. If you have any query or suggestion, kindly revert back with comments.
 
Thanking you.