SQL CLR Functions

We can create a function within SQL Server that depend on a SQL assembly which itself is compiled using any of the .Net framework Common Language Runtime (CLR) managed code.

Beginning with SQL Server 2005, we can write user-defined functions which are of scalar (which returns single value) and table-valued function types. However, in this blog post we are dealing with Scalar type CLR functions.

T-SQL has lot of inbuilt functions and features. However to custom our own complex logic we use any CLR managed code like C# or VB.Net etc., and incorporate it in SQL environment.

Here are the steps to create a Scalar CLR functions:

1. Create a project of type "Class Library" using Visual Studio.

2. Add your static methods in our case "Encrypt" and "Decrypt” methods to the class.

3. Specify "SqlFunction()" attribute to all the functions that can be accessed from SQL Server function/stored procedure.

4. Compile & build the application in "Release" mode to get the assembly (.dll)

5. Now go to SQL Server MS; select your database and create New Query and execute the following statements below to enable CLR:
sp_configure 'clr enabled', 1;
GO
reconfigure
GO
6. If you encounter any compatibility level errors then check to see you database compatibility level using
sp_dbcmptlevel

If it is set to 100 or above, execute the following statement to set it to 90
sp_dbcmptlevel 'SQLCLR', 90

7. Before adding the 'dll' to the SQL assemblies you need to set the database to trustworthy using the following statement
ALTER DATABASE SET TRUSTWORTHY ON

8. Now expand your database node to go to Assemblies located within Programmability and create new assembly.

9. Choose the assembly file path and set the permissions for assembly owner.
(Note: In case you get any errors check the steps #5, #6 and #7)

10. Later to access the external CLR functions from within assembly we need to create a function in SQL Server. External name should be like ...
Execute following queries to encrypt and decrypt functions:

CREATE FUNCTION [dbo].Encrypt(@Input nvarchar(max)) RETURNS nvarchar(max)
EXTERNAL NAME  EDCLR.EDCLR.Encrypt

Go

CREATE FUNCTION [dbo].Decrypt(@Input nvarchar(max)) RETURNS nvarchar(max)
EXTERNAL NAME EDCLR.EDCLR.Decrypt;
 

11. When everything is ready; use the following query to encrypt

Select dbo.Encrypt('Hello World')

and use function to decrypt the encrypted string

Select dbo.Decrypt('i9E2KOEoT7D+Doc2CBdjDA==')

This can be use to encrypt passwords, credit card details and other sensitive information within SQL. Visit MSDN to know further about CLR

Happy Coding :)