SQL CLR project allows the developers to write code in .Net. In this post I will create a SQL CLR function that will return a table as a result.
For those people who did not knew how to create a Sql Clr project first take a look at the following post
Creating Sql Clr Stored Procedure
After creating a SQL CLR project. From the Project Menu or Add New Item dialog box add a new class file. In the class create a function having return type IEnumerable and add the SqlFunction attribute to header of your function as shown below:
public partial class Functions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static IEnumerable fn_getData()
{
ArrayList rowsArray = new ArrayList();
return rowsArray;
}
}
Now add the FillRowMethodName and TableDefinition parameters to the SqlFunction attribute. Set FillRowMethodName = "FillRow". The FillRow method is implicity called by the SQL CLR framework each time the MoveNext() on the returned IEnumerable ( type that implements IEnumerable interface ) is called. In TableDefinition specify the columns of the table. See the below code
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillRow",TableDefinition="Name nvarchar(30), Age int, City nvarchar(30)")]
public static IEnumerable fn_getData()
{
ArrayList rowsArray = new ArrayList();
return rowsArray;
}
Now we will create a function SetData in which we will fill the arraylist with some data.
public static void SetData(ArrayList rowsArray)
{
object[] column = new object[3];
column[0] = "John";
column[1] = 18;
column[2] = "Newyork";
rowsArray.Add(column);
}
After this we will finally write the FillRow method. This method will write the row by row data of ArrayList to the table. It has an object parameter which will give the row of an arrayList. The signature of FillRow method is
private static void FillRow(Object obj, out [col1_type] [column Name], ... )
In FillRow method we will store the obj in an object Array and cast the columns to their respective types as shown below:
public static void FillRow(object obj, out string Name, out int Age, out string City)
{
object[] row = (object[])obj;
Name = (string)row[0];
Age = (int)row[1];
City = (string)row[2];
}
It gets called once each time the framework calls .MoveNext() on the underlying ArrayList method (which implements the inherited IEnumerable interface)
The complete code is below:
public partial class Functions
{
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillRow",TableDefinition="Name nvarchar(30), Age int, City nvarchar(30)")]
public static IEnumerable fn_getData()
{
ArrayList rowsArray = new ArrayList();
SetData(rowsArray);
return rowsArray;
}
public static void FillRow(object obj, out string Name, out int Age, out string City)
{
object[] row = (object[])obj;
Name = (string)row[0];
Age = (int)row[1];
City = (string)row[2];
}
public static void SetData(ArrayList rowsArray)
{
object[] column = new object[3];
column[0] = "John";
column[1] = 18;
column[2] = "Newyork";
rowsArray.Add(column);
object[] column1 = new object[3];
column1[0] = "Scott";
column1[1] = 25;
column1[2] = "Washington";
rowsArray.Add(column1);
}
}
Now deploy the project and move to Sql Server. Open the database in which you have deployed the function. I have used NorthWind database. I ran this query and got the desired output
References:
http://msdn.microsoft.com/en-us/library/ms131103(v=sql.90).aspx
http://blogs.msdn.com/b/stuartpa/archive/2005/07/21/441468.aspx
