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