Create SQL Server store procedure with .NET Framework

We can create store procedures, user defined functions, user defined data types with T-SQL in SQL Server. But T-SQL has limited support and optimization for our business logic implementation. I am not a big fan to write business logic in SQL Server, though some times we need to write some critical logic in SQL Server. For such kind of job or for heavy calculation oriented job T-SQL is not optimized and some time not capable. For that case SQL Server gives us an another option to develop for SQL Server. We can use .NET Framework to build them. Being a Microsoft product SQL Server has support for .NET assembly. But keep in mind that we should use .NET Framework only for that jobs which can not be done using T-SQL. For an quick example you can use regular expression functionality of .NET Framework in SQL Server because SQL Server do not have regular expression support natively. If we use this for simple task which easily can be done using T-SQL then it will be a performance issue. So make decision carefully depending on your situation.

To day I am giving one example of store procedure developing with .NET Framework. For demo purpose (for simplicity of the example program) I am using a simple task which can be easily done using T-SQL. But in real world you should not do this kind of task in .NET Framework rather do it with T-SQL.

To create Store Procedure with .NET Framework you can use any .NET supported programming language of your choice. I am using C# here. Following are the steps which you need to perform.

  1. Create an assembly (.dll) with your .NET Framework code.
  2. Enable CLR in SQL Server.
  3. Load the assembly in to SQL Server.
  4. Create a store procedure with your assembly.
  5. Use that store procedure as usual.

Open Visual Studio and create a ClassLibrary project. You can name it with any name of your choice. Put the following code in side the Class1.cs file.

using Microsoft.SqlServer.Server;

public partial class SqlClrDemo
{
    [SqlProcedure] // Declare that this is a SQL Server SP.
    public static void SayHello()
    {
        // Send the output to SQL Server.
        SqlContext.Pipe.Send("Hello World");
    }
}

Build this code and you will get an assembly with .dll file extension. After that enable the CLR in your SQL Server.

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Then load the assembly in SQL Server from the file location. After this process you can delete your assembly because SQL Server will use the loaded assembly after that.

CREATE ASSEMBLY SayHelloAssembly
FROM 'C:\MyAssembly\SqlClrDemo.dll'
GO

Create a store procedure using this functionality.

CREATE PROCEDURE SayHelloProcedure
AS
EXTERNAL NAME SayHelloAssembly.SqlClrDemo.SayHello
GO

Now execute the store procedure and you can get the output.

EXEC SayHelloProcedure -- Output: Hello World

SQL CLR is a powerful way for SQL Server related development with .NET Framework. Let me know your thoughts and experience with this functionality.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s