Fotia Ltd

Fotia Ltd

Sunday, 24 May 2009

Indispensable SQL CLR functions

posted by Stefan Delmarco

Since the first CTP of SQL Server 2005 I have been building up a library of SQL CLR utilities. Some were just experiments to find the limits of the technology. Others have had tremendous longevity and are part of my daily TSQL repertoire.

The first SQLCLR function I’d like to share deals with a frustration all TSQL programmers have to face on a regular basis. In fact we’re so used to having to work around this obvious TSQL limitation we don’t even notice it anymore. I’m talking about string manipulation.

Everything from simple formatting for reporting to building dynamic SQL (evil but sometimes necessary) highlights how clunky TSQL is when it comes to string manipulation. For example, given a float how difficult is it to output it with a thousands separator to 2 decimal places:

    2 declare @some float;
    3 select @some = rand() * 1E9;
    4 select convert(varchar(20), convert(money, @some), 1);

Building up a trivial dynamic SQL statement can be pretty difficult to debug with all the VB-style concatenation:

    7 declare @database nvarchar(30), @columnFilter nvarchar(30);
    8 select @database = 'AdventureWorks', @columnFilter = 'FirstName';
   10 declare @sql nvarchar(max);
   11 select @sql = 'select * from ' + @database + '.Person.Contact where ' + @columnFilter + ' like @Value';
   12 exec sp_executesql @sql, N'@Value nvarchar(50)', N'Stef%';

There is a better way. With a very simple CLR function we can bring all the power of C#-style string manipulation inline with TSQL. For example:

   15 select @sql = dbo.FormatString1('select * from {0}.Person.Contact where {1} like @Value', @database, @columnFilter);
   16 exec sp_executesql @sql, N'@Value nvarchar(50)', N'Stef%';

A bunch more examples where TSQL string handling is much improved:

   19 -- Date formatting is easy...
   20 select dbo.FormatString0('{0:dd MMM yyyy}', getdate());
   22 -- Can create templates and keep reusing them. Format and args
   23 -- can be separated (not possible with TSQL).
   24 declare @template varchar(500);
   25 set @template = 'Order {0} shipped at {1:dd MMM yyyy} for Account {2}, Total Due {3:N2}';
   27 select dbo.FormatString3(@template, SalesOrderID, ShipDate, AccountNumber, TotalDue)
   28 from Sales.SalesOrderHeader
   29 where SalesOrderId = 44314;
   31 -- Custom formatting is simple, try do this as succinctly in TSQL:
   32 select dbo.FormatString0('{0:0.00%;(0.00)%}',-0.1567);
   34 -- Padding is all in the format string....
   35 select dbo.FormatString0('{0,20}', LastName)
   36 from Person.Contact
   37 where FirstName like 'ste%';
   39 -- The thousands separator example from earlier...
   40 declare @some float;
   41 select @some = rand() * 1E9;
   42 select dbo.FormatString0('{0:N2}', @some);

The source code is pretty trivial:

   15 [SqlFunction(DataAccess = DataAccessKind.None)]
   16 public static SqlString FormatString1(SqlString format, object arg0, object arg1)
   17 {
   18     return format.IsNull ? SqlString.Null : string.Format(format.Value, SqlTypeToNetType(arg0, arg1));
   19 }

Some points to note about the source code:

  • The SqlTypes don’t have any overloads in their ToString implementations which makes custom formatting impossible. To work around this limitation I convert the SqlType to a .NET underlying type.  This is the purpose of the SqlTypeToNetType function. It extracts the .Value property from each of the SqlTypes.
  • Unlike stored procedures, UDF parameters are not optional. Instead of having a single StringFormat function that takes the most number of args I’ll ever need (which will be NULL in most places) I have 4 UDFs: StringFormat0/1/2/3.
  • I’ve created a project on CodePlex called Fotia SQLCLR Library. The full source code is available as well as a SQL script that will deploy the entire library (binaries not required). In the coming weeks I’ll be adding many more very useful SQL CLR functions and user-defined aggregates.


Previous Posts