Simple SP written using CLR in SQL Server 2005

1. Open a C# -- Database project
2. Create a new file into it.
3. Add the below code into it ...

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

public partial class StoredProcedures
{
[SqlProcedure]

public static void SimpleSP()
{
  SqlPipe objPipe;
  objPipe = SqlContext.GetPipe();

 //current month no
  objPipe.Send(System.DateTime.Now.Month.ToString());

 //no of days
  objPipe.Send(System.DateTime.DaysInMonth(2005,7).ToString());
}
};

Just build it and deploy this code. Building and deploying is as simple as just right clicking on the project name and choosing the appropriate option.

Open SQL Server Management Studio

1. Create a new query
2. Try and execute the SP written via VS.NET

use TestDatabase
Exec SimpleSP

It would most probabaly throw an error like this:

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Use sp_configure 'clr enabled' to enable execution of user code in the .NET Framework.

By default execution of the code in .NET framework is disable ... so just run the below two lines to enable it.

sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE

Now try to execute the SP it would work fine and display the numeric value of the current month. Also it would display the number of days in the month of "July".