HOW TO : Call a C# function inside Yukon Stored Procedure

Recently I had an interesting requirement in my project to use a C# procedure inside SQL.Prior to SQL Server 2005, we had to use only Extended store procedures..But as Yukon is integrated with CLR, you can now call a C# Whidbey function inside SQL Server procedure.
The following is the way you can do it:

1. Start Visual Studio 2005.
2. On the File menu, click New Project.
3. Under Projects, choose Visual C# and under Templates choose SQL Server Project template.
Click OK. By default a project named SqlServerProject1 is created.
4. You will now be prompted to specify a connection. Provide the connection of the Yukon
server for which you need the C# function.
5. Right click SqlServerProject1 on Solution Explorer and click Add New Item. Choose any
template that is provided.6. Now replace the following code with the existing code provided
with the template:

#region Using directives

using System;
using System.Collections.Generic;
using System.Text;using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;

#endregionpublic class MyClass{public MyClass(){}

public static void GetDepartmentDetailsByNum(int DeptNo)
{
SqlPipe sp = SqlContext.GetPipe();
sp.Send("The Department number is " + DeptNo.ToString() + "\n");
SqlCommand cmd = SqlContext.GetCommand();
cmd.CommandText = "select * from dept where deptnum=" + DeptNo.ToString();
SqlDataReader rdr = cmd.ExecuteReader();
sp.Send(rdr);
}

public static void GetSum(int x, int y)
{
SqlPipe sp = SqlContext.GetPipe();
int z=x+y;sp.Send(z.ToString());
}
}

7. Now save the project and Build the project.
8. Open SQL Server Management Studio and connect to the Yukon server.
9. Right click the Database where the C# function is needed and then click New Query.
10. In the query editor, paste the following code:
CREATE ASSEMBLY ManagedCsharpProcedureFROM 'C:\Laksk\SqlServerProject1\bin\Debug\SqlServerProject1.dll'
CREATE PROCEDURE [dbo].[GetSum]@x int ,@y intASEXTERNAL NAMESqlManaged.DeptDetails.GetSumGO

11. Now test the GetSum procedure using the following statement :exec GetSum 5,4
Thats it !!!

You now have a C# method being called in SQL Server. The same method can also be used with recordsets as in the C# function GetDepartmentDetailsByNum that is included in the same class. So on passing the Dept Number, we get the Department details through the C# function.