I recently got around to setting up Integrated CLR in my SQL Server 2008 installation, and coding a couple of stored procedures. These notes reflect my first look and a minor “gotcha” to be aware of.

Apparently the performance can be as good as Transact-SQL code in many situations, unless there’s a lot of data access going on. Pipelined functions may even offer a performance advantage in cases where a large amount of data must be delivered to the client.

I’m comfortable with Transact-SQL so for the moment I’ll be using CLR integration only in situations where it offers a very clear advantage over conventional stored procedures and functions. My CLR code will be doing some calculating and manipulating of data rather than just shovelling back the results of basic SQL.

I had a problem with most of the online examples of CLR stored procedures. They demonstrate using SqlContext.Pipe.ExecuteAndSend to return the results of an SQL query directly to the client, but I want to manipulate the data before sending it to the client. Other examples demonstrating how to send a scalar value, a stream of text or a single data record are no more useful.

I finally found what I was looking for at http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlpipe.sendresultsrow.aspx

The above is a teaching example and doesn’t query the database but it has the important information you need to build CLR procedures that can return synthetic data. I adapted it for my own use and the pseudo-code is as follows:

//Create DataTable from an sql query
//Build an SqlDataRecord
//Call method to signal that results will start to be returned
//Loop through rows in the DataTable
//Instantiate an object to process values from the current DataTable row
//Assign values from the business object to the SqlDataRecord
//Send the row to the client
//Call method to signal that results are complete

I won’t provide an example of the above since it’s all straightforward stuff. The code below should be enough to get started.

A key point to remember is that an SqlDataRecord is exactly that – a single record. I started out by thinking of it as a table so I came across a minor “gotcha” which I illustrate in the code samples below. SqlProcedure1 returns the correct data but as a collection of one-line resultsets. I can’t see this being useful very often.

I made a step in the right direction with StoredProcedure2, which returns one resultset, but then I noticed that SqlContext.Pipe.SendResultsRow(record) doesn’t clear the columns. They retain whatever value was last assigned to them. This makes sense since it is a record and we haven’t done anything to clear the columns, we’ve only passed the values to the client. StoredProcedure3 returns on resultset with correct values, and also uses the inbuilt method of setting the columns to Null, avoiding type matching problems.

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 StoredProcedure1()
{
//
//Sends back correct results but one resultset returned
//for each iteration of the for loop
//
SqlMetaData dateCol = new SqlMetaData(“Date”, SqlDbType.Date);
for(int i = 0; i < 10; i++)
{
SqlDataRecord record = new SqlDataRecord(dateCol);
SqlContext.Pipe.SendResultsStart(record);
//Every other row returned should have a null value
if (i % 2 == 0)
{
record.SetDateTime(0, DateTime.Today.AddDays(i));
}
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProcedure2()
{
//
//One resultset returned but where value should be Null
//the value from the preceding row is passed back
//
SqlMetaData dateCol = new SqlMetaData(“Date”, SqlDbType.Date);
SqlDataRecord record = new SqlDataRecord(dateCol);

SqlContext.Pipe.SendResultsStart(record);
for (int i = 0; i < 10; i++)
{
if (i % 2 == 0)
{
record.SetDateTime(0, DateTime.Today.AddDays(i));
}
SqlContext.Pipe.SendResultsRow(record);
}
SqlContext.Pipe.SendResultsEnd();
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProcedure3()
{
//
//One resultset returned with correct values
//
SqlMetaData dateCol = new SqlMetaData(“Date”, SqlDbType.Date);
SqlDataRecord record = new SqlDataRecord(dateCol);

SqlContext.Pipe.SendResultsStart(record);
for (int i = 0; i < 10; i++)
{
if (i % 2 == 0)
{
record.SetDateTime(0, DateTime.Today.AddDays(i));
}
else
{
//avoids problem with non-nullable data type
record.SetDBNull(0);
}
SqlContext.Pipe.SendResultsRow(record);
}
SqlContext.Pipe.SendResultsEnd();
}

};