CLR Stored Procedures

Common Language Run time CLR Stored Procedures are a .NET Framework implementation stored procedures that extends the functionality of T-SQL. Unless it is absolutely important or there is advantage to have CLR Stored Procedures around there is no need to work on two different environments and introduce extra layer complexity to your stored procedures.
Let us convert one of T-SQL stored procedure from our previous blog to CLR Stored Procedure on Visual Studio
Open Visual Studio select SQL Server, Visual C# CLR Database Project the fill your solution information and click on OK. You could also work with Visual Basic

Right click on your project, Add, Stored Procedure…
In the next dialog enter your stored procedure name then click on Add

Before we begin we need to add database reference to the project in the dialog select from your list of references or click on Add New Reference…

Then confirm debugging for the project if you are going to do that

We will have our CLR store procedure code will open in the new window. CLR stored procedure are public class with public static void method.

Write your procedure as follows

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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 CustomerAccount_CLRProc(SqlInt32 CustomerID)
    {       
      using(SqlConnection connection = new SqlConnection("context connection=true")) 
      {
         connection.Open();
         SqlCommand command = new SqlCommand("SET NOCOUNT ON; " +
	                                         "SELECT P.FirstName,P.LastName,C.AccountNumber " +
	                                         "FROM [Person].[Person] P " +
		                                     "     JOIN [Sales].[Customer]  C " +
		                                     "     ON P.BusinessEntityID = C.PersonID " +
	                                         "WHERE C.CustomerID ="+ CustomerID	, connection);
         SqlDataReader reader = command.ExecuteReader();
         SqlContext.Pipe.Send(reader);
      }
    }
};

Then click on Build menu and click on Deploy CLRDemo (our CLR Stored Procedure name). Before you do that make sure your firewall allow remote debugging.


Once it is deployed you will get the confirmation. Now open of SSMS and you should find the CLR stored procedure

Now let us enable execution permission and try executing our stored procedure

--enable execute permission permission  on the .NET stored procedures--
use [AdventureWorks2012]
GO
sp_configure 'clr enabled',1
GO
RECONFIGURE
GO
--execute--
exec dbo.CustomerAccount_CLRProc 21139

Even though our main topic is stored procedure we also have CLR functions. Let us write trim function so that we could have a one function that will replace ltrim and rtrim.

[SqlFunction]
public static string trim (string inputText){
    return inputText.Trim();
}

Deploy the function and let us try it from SSMS

--Execute
declare @txt varchar(30) = '         test    '
select dbo.trim(@txt) withtrim,@txt withouttrim,len(dbo.trim(@txt)) withtrim,len(@txt) withouttrim

Well it is not quite as we need it because as every other user defined function our trim has to be executed with the schema and the function name.
Read more:
http://technet.microsoft.com/en-us/library/ms131094.aspx