One the three use cases I commonly give as to when you'd want to use SQLCLR is that it can make solving certain problems easier than T-SQL solutions because they leverage something the base class library. Commonly this results in less code which performs better. Parsing a comma-delimited string into a set of rows is a pretty good example. There's lots of examples showing how to do that with T-SQL, but not many showing SQLCLR. My solution is to create a Table Valued Function (TVF) that maps to CLR-based class that implements two methods: one that uses the .Split method on a string to return an IEnumerable instance and a second that SQL Server calls with each value in the source string to return the values for use in a row. Here's the example of that:
public partial class CommaDelimitedParser
{
// TVFs require two methods, one that returns IEnumerable
// that returns some collection of values. Can be called
// anything, but by convention is called InitMethod since
// it intializes an Enumerable collection of values.
// This value must have an attribute that a points at
// another method that takes a single object out of that
// collection and returns it as an output parameter.
[SqlFunction(FillRowMethodName = "GetString",
TableDefinition ="value nvarchar(max)",
Name = "CommaDelimitedParser")]
public static IEnumerable InitMethod(SqlString SourceList)
{
return SourceList.Value.Split(',');
}
// This function then gets called one for each value in
// in the collection and writes a row into the output
// resultset by setting the value of an "outted" parameter.
public static void GetString(Object obj, out SqlString value)
{
value = (string)(obj);
}
You can use this function in a query like this:
declare @a1 varchar(max)
set @a1 = '1,2,3'
select * from dbo.CommaDelimitedParser(@a1)
That's all well and fine for just one comma delimited list, but how can you merge three lists into a single to use? Ah, that's part II.