Procedures and Functions on the CLR

Creating stored procedures out of a .NET Framework language to run on the CLR is just as easy to do. If you are not familiar with the .NET Framework read Chapter 11, which introduced the support for the .NET Framework in SQL Server 2005 and kicked off the discussion with examples creating data types and aggregates in C#. The examples provided here are written in C#, but you can use any .NET language for your stored procedures.

The first thing you have to do before you can execute anything on the SQL Server CLR is enable it (the scope is server-wide). To enable the CLR execute the following T-SQL code in Management Studio.

 sp_configure 'clr enabled', 1 GO RECONFIGURE GO

Now you can use Visual Studio to create your code. Create a solution for creating class libraries, and set up a new project specifically for stored procedures. Call the class StoredProcedures or something similar. In the class that is created, you will need the following directives:

 using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server;

Now add the definition for a partial class (if Visual Studio has not already done so).

 public partial class StoredProcedures {     [Microsoft.SqlServer.Server.SqlProcedure]     public static void GetCurrentDate()     {         //Put your code here     } };

Now add some code.

 [Microsoft.SqlServer.Server.SqlProcedure]     public static void GetCurrentDate()     {         SqlPipe p = SqlContext.Pipe;         p.Send (System.DateTime.Today.ToString());     }

The stored procedure class is now ready and must be installed to SQL Server. This can be done as follows in T-SQL using Management Studio:

 CREATE ASSEMBLY [StoredProcedures] FROM 'C:\Documents and Settings\shapj015\My     Documents\Visual Studio 2005\Projects\StoredProcedures\StoredProcedures\bin\Debug\StoredProcedures.dll' WITH PERMISSION_SET = SAFE; GO

This code installs your assembly to SQL server. Once the assembly has been installed (you can see it under the assemblies folder in your specific database), you can add the stored procedure into SQL Server as follows:

 CREATE PROCEDURE [dbo] . [GetCurrentDate] AS EXTERNAL NAME [StoredProcedures].[StoredProcedures].[GetCurrentDate]; GO

The above stored procedure simply returns the current date (of course the built-in function GetDate() does that as well but we needed a simple example). Now let’s get a little more sophisticated and create a stored procedure that takes a parameter. Note the use of the SqlPipe construct, which is used to return data to the client that called the stored procedure.

 [Microsoft.SqlServer.Server.SqlProcedure] public static void GetFormattedDate(int Option) {     string s = System.DateTime.Today.ToString();     SqlPipe p = SqlContext.Pipe;     switch (Option)     {         case 1:             s = s.Remove(10);             p.Send(s);             break;         case 2:            //other options         default:             break;     } }

To install this parameter-driven procedure, you need to specify the parameter in T-SQL as follows:

 CREATE PROCEDURE [dbo].[GetFormattedDate] (     @Option int ) AS EXTERNAL NAME [StoredProcedures].[StoredProcedures].[GetFormattedDate]; GO

You can call either procedure now as follows:

 exec GetCurrentDate exec GetFormattedDate 1

Let’s now look at CLR-based UDFs. The GetFormattedDate stored procedure is actually a better candidate for a CLR function than it is as a stored procedure. So let’s reimplement it accordingly.

 [SqlFunction(DataAccess = DataAccessKind.Read)] public static SqlString GetFormattedDate(int Option) {     string s = System.DateTime.Today.ToString();     switch (Option)     {         case 1:             s = s.Remove(10);             break;         case 2:            //other options         default:             break;     } return s; }

Creating the assembly and installing it to SQL Server is the same process as described earlier for stored procedures (see also Chapter 11). The T-SQL code for installing the function is a little different.

 CREATE FUNCTION [dbo].[GetFormattedDate](@option int) RETURNS [nvarchar] (10) WITH EXECUTE AS CALLER AS EXTERNAL NAME  [Functions].[Functions].[GetFormattedDate]

Remember, always make sure you specify the exact path to the function when installing the function; that is, the namespace, class and method, or you will get an error that the function cannot be found.