SQL SERVER – Introduction to CLR – Simple Example of CLR Stored Procedure

October 19, 2008

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

CLR is abbreviation of Common Language Runtime. In SQL Server 2005 and later version of it database objects can be created which are created in CLR. Stored Procedures, Functions, Triggers can be coded in CLR. CLR is faster than T-SQL in many cases. CLR is mainly used to accomplish task which are not possible by T-SQL or can use lots of resources. CLR can be usually implemented where there is intense string operation, thread management or iteration methods which can be complicated for T-SQL. Implementing CLR provides more security to Extended Stored Procedure.

Let us create one very simple CLR where we will print current system datetime.

1) Open Microsoft Visual Studio >> Click New Project >> Select Visual C# >> Database >> SQL Server Project

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure clr1

2) Either choose from existing database connection as reference or click on Add New Reference. In my example I have selected Add New Reference.

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure clr2

3) If you have selected existing reference skip to next step or add database reference as displayed in image.

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure clr3

4) Once database reference is added following project will be displayed in Solution Explorer. Right click on Solution Explorer >> Click on Add >> Stored Procedure.

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure clr4

5) Add new stored procedure template from following screen.

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure clr5

6) Once template added it will look like following image.

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure clr6

7) Now where it suggest to //Put your code here. Replace it with code displayed in the image. Once the code is complete do following two steps.
a) Click on menu bar >> Build >> Build ProjectName
b) Click on menu bar >> Build >> Deploy ProjectName
Building and Deploying project should give successful message.

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure clr7
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure] public static void CLRSPTest()
{
SqlPipe sp;
sp = SqlContext.Pipe;
String strCurrentTime = “Current System DateTime is: “
+ System.DateTime.Now.ToString();
sp.Send(strCurrentTime);
}
};
8) Now open SQL Server Management Studio and run following script in Query Editor. It should return current system datetime. Running it again the time will change.