One of the most powerful tool that was introduced in the SQL Server 2005 was the use of CLR Stored procedures. We all are aware of the normal stored procedures that we use in our applications. CLR stored procedures are a new feature in the SQL stored procedure, which allows us to write a code in C# and execute them as an SQL stored procedure. You can right any complex logic or use .NET classes to solve a problem which might have been difficult or not possible at all to do using the normal stored procedures.
For ex : For our simplicity, we will create a stored procedure which will write some text in a notepad file using the .NET classes of System.IO. So let’s start :
2. Next it will ask to create a Reference for a Database or use an existing database for this purpose. The point to note here is that this is the database where the stored procedure will be published or deployed when the application is used.
3. Press Ok and then Yes. So it will create a Project of type CLR based stored procedure
5. Note the attribute [Microsoft.SqlServer.Server.SqlProcedure] above the name of the static function. This signifies that the function define is of type CLR Stored procedure. In this way, we can add more functions and add this attribute to mark them as Stored procedures.
6. Write the code inside the function. Like our sample, I have written a sample code to write some text in a notepad file. Build the solution.
7. Now our procedure is ready with the logic that we needed to use. To use this procedure, we will be adding this to the SQL database, for which we are going to use this. To do this, we use the Deploy functionality of the project.
8. Go to Build option at the top and select the Deploy option.
9. Now you can see the store procedure in the SQL Server database, under the Programmability section. Now you can execute this stored procedure like any other stored procedure.
Important points :
These points imply the issues that you may face during the deployment or execution of the stored procedures.
- By default, the CLR stored procedures may be disabled in your SQL server. To enable it, use the following commands :
SP_CONFIGURE ‘clr enabled’, 1
- You will be required to change the permissions level of this project type to External. This will allow the SP to access the resources that it requires, outside the SQL Server. There are also other levels of permissions but those are beyond the scope of this example i feel. Also to be honest, I do not have much knowledge about these. So, to change the permission level, right click on the solution and select the Database tab. Change the Permission Level to External, as below :
- You may also face the following issue :
CREATE ASSEMBLY for assembly ‘assembly_name’ failed because assembly ‘CLRStoredProcedureSample’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.
In order to avoid this, you will be require to run the following code :
ALTER DATABASE Database_Name_Here SET TRUSTWORTHY ON
So in this way you can create the stored procedures, User defined functions, triggers etc. also. Like normal stored procedures, these are also pre-compiled and managed code. So they are provide the benefits of the normal stored procedures as well as the use of the C# code to manage the complex logic.