CLR Stored Procedure - SQL Programmers

CLR is the abbreviation of Common Language Runtime. It is the heart of the .Net Framework. The following are the uses and responsibilities of CLR

• Garbage Collection
• Code Access Security
• Code Verification
• Intermediate Language
• Code stored procedure and functions
• Faster that standard Sql
• Used in string manipulations
• Security for Extended stored procedure

CLR Stored Procedure

In the Common language runtime CLR stored procedure will be implemented as public static methods on a class in a Microsoft .Net Framework Assembly. Static Methods can be declared as void or return an integer value.

We can get the return value from the procedure and it can be stored in the variable.

Example

Execute @status=Procedure_Name

The @status variable will hold the value returned by the method. Suppose the method is declared as void then the 0 will be returned.

We can pass any type of Parameters which are supported in native SQL Type.

Now we can create one small CLR to display ‘Hello World!’

Steps to create CLR Stored procedure

	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 CLRWelcome()
	    {
	        SqlPipe sp = SqlContext.Pipe;
	   String strValue = "Welcome to CLR!";
	        sp.Send(strValue);
	    }
	};