SQL SERVER – Introduction to CLR – Simple Example of CLR Stored Procedure

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)

About these ads

37 thoughts on “SQL SERVER – Introduction to CLR – Simple Example of CLR Stored Procedure

  1. 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

    Like

    • u can use the following query:

      EXEC sp_configure ‘show advanced options’ , ‘1’;
      reconfigure;

      EXEC sp_configure ‘clr enabled’ , ‘1’ ;
      reconfigure;

      EXEC sp_configure ‘show advanced options’ , ‘0’;
      reconfigure;

      Like

    • –ad-hoc-update-to-system-catalogs
      sp_configure ‘user options’, 1
      reconfigure with override;
      –allow user
      sp_configure ‘Allow Updates’, 1;
      reconfigure with override;
      –enable clr for sql clr run
      sp_configure ‘clr enabled’, 1;
      reconfigure with override;

      sp_configure

      Like

  2. 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?

    Like

  3. 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?

    Like

  4. 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!

    Like

  5. 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

    Like

  6. 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.

    Like

  7. Are you saying that you need a full-blown version of Visual Studio to work with CLRs? I only have the Visual Studio that comes with SQL 2008 R2 and it does not have a Visual C# project type.

    Like

  8. Considering you are a proven expert, I wish you would have tried to add something more to this really simple example that can be found almost everywhere! but thanks for the effort.

    Like

  9. “CLR is faster than T-SQL in many cases”
    Sorry but here i can’t agree with you. T-SQL is used mostly in stored procedures or functions, which they are stored on the server it’s self. Wright noe i am ‘translating’ a stored procedure from SQL into a CLR function, and after i tested it severl times the results are aprox:
    10 s for the SQL
    35 s for the CLR

    One of the time consuming effect for the CLR is opening/closing the readers, executing commands and all the other things that you must do in order to execute the SQL querys.
    But i think CLR are one of the best solutions if you want acces to a database from C#.

    Like

  10. When Exec dbo.clrsptest is run it puts the result in the messages tab in sql server management studio. How would one get the result into a table? This did not work.

    Use [vc]

    Create Table #t (date nvarchar (255))
    Insert Into #t
    Exec dbo.clrsptest

    Select * from #t
    Drop Table #t

    Like

  11. Hi…. good article….it helped me alot….but,

    i want to use a global variable in the procedure…. how can i do this….?
    eg:

    public static string strr = “hhhiii”;

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void sp_GetHexValue(int number)
    {
    strr = “hhhhhiiiii”;
    SqlContext.Pipe.Send(strr);
    }

    i’m getting an error while deploying.

    ” Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6211, Level 16, State 1, Line 1 CREATE ASSEMBLY failed because type ‘StoredProcedures’ in external_access assembly ‘TestSQL_CLR_Project’ has a static field ‘strr’. Attributes of static fields in external_access assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language.
    An error occurred while the batch was being executed.”

    so, i tried giving readonly property…. it worked…. but, i’m unable to set the value at runtime.

    how can i achieve this ….?

    Thanks 4 ur help.

    Like

  12. Hi i am facing issue after clr deployment

    my user has sysadmin right
    but i am not dbowner of database
    i change sp_changedbowner to my user and deploy that and then re change to original one.
    Assembly is UNSAFE, database is Trustworthy on and clr is on

    but execution with dbowner user stored procedure gives below error
    dbowner is not sysadmin.

    Msg 10314, Level 16, State 11, Line 4
    An error occurred in the Microsoft .NET Framework while trying to load assembly id 65672. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
    System.IO.FileLoadException: Could not load file or assembly ‘pssqlcustmization, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
    System.IO.FileLoadException:
    at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
    at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
    at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
    at System.Reflection.Assembly.Load(String assemblyString)

    if i change that dbowner and gives right to sysadmin then it works.
    but that’s not what i am looking for.

    i tried to set permission on database that execure, create assembly and alter any assembly but error continues.

    Can you let me know which right need to assign when it’s not sysadmin
    I already checked MSDN and other blogs but not get good idea for practical situations.

    i create stored procedure with EXECUTE AS CALLER.

    Like

  13. Hi Pinal

    Thanks for detail help on Extended stored procedure, I have a query related to build. My client does not allowed me to install visual studio on production environment so there is any option to generate DLL on local PC with Local database and implement at production server.

    Pl. help me on this issue.

    Regards
    Rajesh Sheth

    Like

  14. Hi,

    You can copy DLL file from VS project BIN folder, and register this DLL asembly on SQL Server using SQL commands. Example:

    create ASSEMBLY SQLCLRTest1 from ‘c:\temp\SQLCLRTest1.dll’ WITH PERMISSION_SET = SAFE
    go

    create procedure YOURCLRPROCEDURENAME(@YOYRPARAMETERNAME int)
    as
    external name
    [SQLCLRTest1].[StoredProcedures].[YOURC#PROCEDURENAME]
    go

    After that you can delete DLL file from SQL Server. Regards.
    Mariusz

    Like

  15. Excellent article and I got it to work with no problem.
    What I need to do is pass in 1 parameter and then do a select from a table and return exactly 1 value. What would be the preferred means of doing this? Do you have an example? I can’t find any on the net for this.

    Like

  16. Shantanu Choudhary
    I had created clr using T-SQL but when i am executing the store procedure in T-SQL query window i am getting an


    Msg 6522, Level 16, State 1, Procedure CLRSPTest, Line 0
    A .NET Framework error occurred during execution of user-defined routine or aggregate “CLRSPTest”:
    System.Security.SecurityException: Request for the permission of type ‘System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ failed.
    System.Security.SecurityException:
    at System.Security.CodeAccessSecurityEngine.CheckNReturnSO(PermissionToken permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 unrestrictedOverride, Int32 create)
    at System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission cap, StackCrawlMark& stackMark)
    at System.Security.CodeAccessPermission.Assert()
    at StoredProcedures.CLRSPTest()

    In CLR fuction i had written a code

    string MachineName = System.Environment.MachineName.ToString();

    Can any one suggest me why this is happing

    Like

  17. Pingback: SQL SERVER – Weekly Series – Memory Lane – #051 | Journey to SQL Authority with Pinal Dave

  18. Great article! However I’m having trouble getting Database templates to show up in Visual Studio 2008. I have SQL Server 2008 R2. The initial install may have been Express – the instance indicates it is. I’ve tried updating SQL Server, and installed newer versions of VS, but cannot get database templates to show up. I’ve also run devenv.exe /installvstemplates at the VS command prompt (32 & 64). Is my version of SQL Server the issue maybe? I’ve spend days on this :-(

    Like

  19. Hi pinal please me out i am stuck in “HOW TO HANDLE ERRORS IN USER-DEFINED FUNCTION”.

    i know we can’t handle the errors in functions but how to handle error occurred at nth line of function?

    Like

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