CLR Stored Procedure in SQL Server

Introduction

In SQL Server 2005 and later version, we will be able to create CLR database object (function, stored procedure, trigger, etc.). Sometimes, CLR database object is faster than T-SQL. Mainly CLR database object can be used when we have to implement logic which is not possible with T-SQL or it can use more resources.

Advantages

• CLR Stored Procedures are managed codes so it ensures type safety, memory management, etc.
• It provides OP capability hence it enables encapsulation, polymorphism & inheritance.
• CLR stored procedure can be written in C#, VB.NET or in any other language which is supported by the .NET Framework.
• It is very useful while executing complex logic like intense string operation or string manipulations, cryptography, accessing 3^rd party library, accessing system resources and file management, etc.

Disadvantages

• Deployment may be difficult in some scenarios.
• It is not convenient in all contexts like it should not be used to execute simplest queries.

Creating CLR Stored Procedure

Using Microsoft Visual Studio, we can create SQL server database project. Within this project, we can add “SQL CLR C# stored procedure”.

SqlContext and SqlPipe Class

SqlContext class represents an abstraction of the caller contest that provides access to the SqlPipes, SqlTriggerContext and windowsIdentityobject. 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 (Microsoft Windows identity of the calling client).

SqlPipe class is useful to return result back to the caller. Sqlcontext class has pipe property which has type SqlPipe class. This class has method called "Send"; it helps to set result directly to the client or current output consumer. This method is able to send SqlDataReader object, SqlDataRecord object and message string.

Hello World Example

Using SqlPipe.Send(string) method, we can send message to client application. Here, length of text is limited to 8000 characters. If text exceeds 8000 characters, 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");
    }
}

Steps to Deploy CLR Stored Procedure

Step 1

Enabling CLR Integration:

Use <database name="">
SP_CONFIGURE 'clr enabled',1 

Step 2

Set database to TRUSTWORTHY if our assembly depends on other 3^rd party assembly.

ALTER DATABASE <<database name>> SET TRUSTWORTHY ON

Step 3

Matches SID in database.

Perhaps the database owner SID recorded in the master database differs from the database owner SID recorded in database. We detach or backup database from instance of SQL server (say Server A) and attach or restore that database to other instance of SQL server (says server B). In this scenario, we get an error "The database owner SID recorded in the master database differs from the database owner SID".

The following script is helping us to update 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) </loginname>

Step 4

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

Step 5

Create Assembly

In the following Query “<<Local folder path >>”, we need to replace path where precompiled assembly is copied (in Step 4).

Note: Before we drop the assembly, we need to remove all the references of this assembly.

-- Create Assembly
IF (EXISTS(select * from sys.assemblies where name = 'SQLCLR'))
BEGIN
	-- remove the reference 
	IF(EXISTS(select * from sys.objects where name = ' HelloWorld' and type='PC'))
		DROP PROCEDURE HelloWorld
	DROP ASSEMBLY SQLCLR
END

CREATE ASSEMBLY SQLCLR FROM '<<local folder>>\SQLCLR.dll' with PERMISSION_SET =UNSAFE;

Step 6

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]

Output of Hello World example

Tabular Result Example

Using ExecuteAndSend and Send (SqlDataReader) method of SqlPipe object, we can send the result of a query directly to the client. Here, data will transfer 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);
}

Follow the steps 4, 5 and 6 to register the assembly and create CLR procedure.

Output

Passing parameter to SQL CLR Procedure

We can pass parameter to CLR procedure, same as using "OUTPUT" parameters we can get the result from the stored procedure. We must specify the parameter with “out” attribute to represent an OUTPUT parameter in CLR stored procedure.

CLR procedure Code

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

Output

Conclusion

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