CLR Stored Procedures in SQL Server

Introduction

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.

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.

Brief introduction to SqlContext and SqlPipe Class

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

The SqlPipe class is useful for returning the result back 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 is able to 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 SID recorded in the master database may differ from the database owner SID recorded in the database. This can happen when we detach or backup 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 database owner 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 their 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 to (in Step 4).

Note: Before dropping the assembly, we need to remove all the references of 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:

Using the “SqlPipe.Send(string)” method, we can send a message directly to the client application. Here the length of 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. Here the data will be transfered 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 output of the parameter uses the "OUTPUT" keyword. We need to 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 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.

Conclusion

The CLR procedure can be used in complex business scenarios that is not possible with T-SQL.