How to Create CLR Stored Procedure

Since SQL Server 2005, developers can write stored procedures using .NET languages. In this post, I will show you how to create stored procedure using C# using SQL Server 2008 R2 and Visual Studio 2012. If you use newer version of SQL Server, the solution should also be applied. I will create factorial function as applied in Math such as:

5! = 5 * 4 * 3 * 2 * 1 = 12

First, we have to download SQL Server Data Tools (SSDT). At this time of writing, I use SSDT for Visual Studio 2012 October Release which can be downloaded from here.

Then, launch SSDT setup and wait until finished.

Launch Visual Studio 2012, create new project, select SQL Server template, and select SQL Server Database Project.

Since I’m using SQL Server 2008R2, I have to change target framework to .NET Framework 3.5. Change the name to SPFaktorial and click OK to create the project, Visual Studio 2012 will create the Database Project for you.

In Solution Explorer, right click in project, click Add – New Item, select SQL CLR C#, then select SQL CLR C# User Defined Function.

Change the name to Faktorial and click Add, Visual Studio will create a C# file contains some dummy codes. We can change this code to our factorial function:

   1:  using System;

   2:  using System.Data;

   3:  using System.Data.SqlClient;

   4:  using System.Data.SqlTypes;

   5:  using Microsoft.SqlServer.Server;

   6:   

   7:  public partial class Matematika

   8:  {

   9:      [Microsoft.SqlServer.Server.SqlFunction]

  10:      public static SqlInt64 Faktorial(int f)

  11:      {

  12:          if (f == 1)

  13:              return f;

  14:   

  15:          return f * Faktorial(f - 1);

  16:      }

  17:  }

Our factorial function is a recursive function, it will call itself until the factorial number turns 1.

Let’s build the project! Select Build menu, then select Build Solution. If you see in project folder, Visual Studio will create Debug folder which contains compiled .NET assembly inside.

Actually, we can directly deploy our project to SQL Server, but in this post I will show you how to manually deploy our .NET assembly to SQL Server.

Lets launch the SQL Server Management Studio and connect to the Database Engine and open New Query window, then issue this command:

exec sp_configure 'clr enabled', 1
go
reconfigure
go

The command above will enabling CLR feature (which is disabled by default).

At this point, we are ready to deploy the .NET assembly to SQL Server using CREATE ASSEMBLY:

create assembly SPFaktorial
from 'D:\Docs\visual studio 2012\Projects\SPFaktorial\SPFaktorial\bin\Debug\SPFaktorial.dll'

Press F5 to run the command, then we have to create T-SQL function to wrap the CLR method:

create function Faktorial(@i int) returns bigint
as external name SPFaktorial.Matematika.Faktorial

Press F5 to run the command.

At this point, the .NET assembly has been deployed successfully to SQL Server, the next step is to test it:

Voilla….our C# method has been successfully executed inside SQL Server.

The sample code can be downloaded from here.