CLR Stored Procedures in SQL Server

Introduction

In SQL Server 2005 and later versions, we can create CLR database objects, such as 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 impossible with T-SQL, or it can use more resources.

Creating CLR Stored Procedure

Using Microsoft Visual Studio, we can create a SQL Server database project. By adding a "SQL CLR C# Stored Procedure" item to the project, we can create a CLR Stored Procedure.

What are SqlContext and SqlPipe Classes?

SqlContext class represents an abstraction of the caller context that provides access to the SqlPipes, SqlTriggerContext, and windowsIdentityobject. This class object can obtain the pipe object, trigger context, and windows identity (the Microsoft Windows identity of the calling client). The SqlContext class is internally sealed, so it cannot be inherited.

The SqlPipe class helps return the result to the caller. The Sqlcontext class has the pipe property that has the type SqlPipe class. The "Send" method of the SqlPipe class helps us to send the result directly to the client or the current output consumer. This method can send a SqlDataReader object, SqlDataRecord object, and message string.

Procedure to deploy a CLR Stored Procedure

Step 1

Enable CLR Integration as in the following:

Use <<Database Name>>  
SP_CONFIGURE 'clr enabled',1

Step 2

Set the database to TRUSTWORTHY if the assembly depends on any other third-party assembly.

ALTER DATABASE <<Database Name>> SET TRUSTWORTHY ON

Step 3

Match the SID in the database.

The database owner's SID recorded in the master database may differ from the SID recorded in the database. This can happen when we detach or back up the database from an instance of SQL Server (say server A) and attach or restore that database to another instance of SQL Server (say server B). In that case, we get the error "The database owner SID recorded in the master database differs from the SID."

The following script helps to update the correct SID.

Use <<Database Name>>  
  
DECLARE @Command VARCHAR(MAX) = 'ALTER AUTHORIZATION ON DATABASE::<<DatabaseName>> TO   
[<<LoginName>>]'   
  
SELECT @Command = REPLACE(REPLACE(@Command   
            , '<<DatabaseName>>', SD.Name)  
            , '<<LoginName>>', SL.Name)  
FROM master..sysdatabases SD   
JOIN master..syslogins SL ON  SD.SID = SL.SID  
WHERE  SD.Name = DB_NAME()  
  
EXEC(@Command)

Step 4

Copy the CLR function library and its dependent library files to the local folder on the database server.

Step 5

Next, create the Assembly. In the following query, "<<Local folder path >>" must be replaced with the path where the precompiled assembly has been copied (in Step 4).

Note. Before dropping the assembly, we need to remove all the references to this assembly.

-- Create Assembly  
IF (EXISTS(select * from sys.assemblies where name = 'SQLCLR'))  
BEGIN  
    -- Before drop assembly remove the references  
    DROP ASSEMBLY SQLCLR  
END  
  
CREATE ASSEMBLY SQLCLR FROM '<<Local folder path>>\SQLCLR.dll' with PERMISSION_SET =UNSAFE;

Step 6

Next, create the create CLR Procedure.

IF(EXISTS(select * from sys.objects where name = ' HelloWorld' and type='PC'))  
    DROP PROCEDURE HelloWorld  
GO  
CREATE PROCEDURE [dbo].[HelloWorld]  
AS EXTERNAL NAME [SQLCLR].[StoredProcedures].[HelloWorld]

Hello world example

We can send a message directly to the client application using the "SqlPipe.Send(string)" method. Here the length of the text is limited to 8000 characters. If the text exceeds 8000 characters, then it will be truncated.

CLR procedure Code

public partial class StoredProcedures  
{  
    [Microsoft.SqlServer.Server.SqlProcedure]  
    public static void HelloWorld ()  
    {  
        // Put your code here  
        SqlContext.Pipe.Send("This is my CLR SP test");  
    }  
}

Create the library (assembly) file by building the project and following Steps 4, 5, and 6 (defined in the preceding section) to register the assembly and create the CLR procedure.

The following is the output of the Hello World example.

Tabular result Example

Using the "ExecuteAndSend" and "Send (SqlDataReader)" methods of the SqlPipe object, we can send the query result directly to the client. The data will be transferred directly to the network buffers without being copied to managed memory.

CLR procedure Code

Microsoft.SqlServer.Server.SqlProcedure]  
public static void GetAllEmployees()  
{  
    SqlConnection con = new SqlConnection("context connection=true");  
    con.Open();  
    SqlCommand cmd = new SqlCommand("select * from employee", con);  
    SqlDataReader reader = cmd.ExecuteReader();  
    SqlContext.Pipe.Send(reader);  
}

Create the library (assembly) file by building the project and following Steps 4, 5, and 6 (defined in the preceding section) to register the assembly and create the CLR procedure.

Output

Passing parameter to SQL CLR Procedure

The same as for a SQL Stored Procedure, we can also pass a parameter to a CLR procedure, and the parameter output uses the "OUTPUT" keyword. We must specify the parameter with an "out" attribute to represent an OUTPUT parameter in the CLR Stored Procedure.

CLR procedure Code

[Microsoft.SqlServer.Server.SqlProcedure]  
public static void GetValue(SqlInt32 value, out SqlString retValue)  
{  
    retValue = "You have entered : " + value;  
}

Create a library (assembly) file by building the project and following Steps 4, 5, and 6 (defined in the preceding section) to register the assembly and create the CLR procedure.

Output

Advantages of CLR Stored Procedure

CLR Stored Procedures are managed code, to ensure type safety, memory management, etc.
It provides Object Oriented Programming capability, enabling 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 beneficial when executing complex logic like intense string operations or string manipulations, cryptography, accessing a third-party library, accessing system resources and file management, etc.

Limitations of CLR Stored Procedure

Deployment may be complex in some scenarios.
It is inconvenient in all contexts, such as it should not be used to execute the simplest queries.

Conclusion

The CLR procedure can be used in complex business scenarios that are impossible with T-SQL.