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
2) Either choose from existing database connection as reference or click on Add New Reference. In my example I have selected Add New Reference.
3) If you have selected existing reference skip to next step or add database reference as displayed in image.
4) Once database reference is added following project will be displayed in Solution Explorer. Right click on Solution Explorer >> Click on Add >> Stored Procedure.
5) Add new stored procedure template from following screen.
6) Once template added it will look like following image.
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.
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.
USE AdventureWorks
GO
EXEC dbo.CLRSPTest
GO
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
Can a SQL CLR stored procedure call or excute a SQL procedure or function?
Thanks for the post Pinal, I make a point to read your blog very useful stuff. I hope you dont mind me putting reference’s to other blogs
Hi friends
In case you run into the same problem as i did .
i.e. at command prompt if you get
“Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option.”
then you can correct that by
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125208&SiteID=1
or
http://geekswithblogs.net/vrod/archive/2006/07/17/85471.aspx
hello pinal. one thing puzzled me a little bit, i really need to ask this:)
you wrote “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.”
now, if i save a stored procedure in sql server management studio, isnt it saved as a clr function? up to now i always knew that stored procedures are “compiled”, but your sentences made me understand as if the sp way is similar to hardcoding the sql query text in code and execute it with a SqlConnection object.
can you clarify this point a little bit more please?
hello pinal. one thing puzzled me a little bit, i really need to ask this:)
you wrote “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.”
now, if i save a stored procedure in sql server management studio, isnt it saved as a clr function? up to now i always knew that stored procedures are “compiled”, but your sentences made me understand as if the sp way is similar to hardcoding the sql query text in code and execute it with a SqlConnection object.
can you clarify this point a little bit more please?
Hi,
Dave (Pinal). Thanks to God for bringing you to earth, you have a perculiar way of making programmers life easy, all over the web, there are lots of bla-bla..bla, but every argument you treat, is always a strait and simplest form to a solution.
Keep up with it. YOU ARE MY OBAMA!
Enjoyed post. I’m wondering why it is so difficult to find an example ( in literature or Internet at large ) of converting a T-SQL procedure which contains an “out” variable into an identicale C# CLR stored procedure. Is there something which prohibits this conversion ? If not, can you provide or cite an example ? Real world T-SQL procedures tend to have out parameters. Thx
Thanks for a brilliant article Pinal. Very helpful.
Interesting article, I was wondering if this is possible for a problem which I need to read from the different database to another database. Both database is in SQL Server.