SQLCLR wrapper for RAISERROR()

Recently I was writing a SQLCLR stored procedure. I made several calls to RAISERROR() in the procedure. While this meant that my stored procedure would be easier to debug in the future because of useful error messages, there was a lot of ceremony involved in these RAISERROR() calls. Therefore I decided to encapsulate all this ceremony into a simple static method.

RAISERROR() presents a small challenge in wrapping in a C# function. That challenge is the variable parameter length due to the printf() like parameter substitution. The first step to overcoming this is to use the params keyword for the substitution parameters. The second step is avoiding the possibility of SQL Injection.

Many built in CLR functions use the params keyword. For example, if you use the String.Format(), Console.WriteLine() or Debug.Print(), with substitution parameters you have used overloads with the params keyword. For example, lets look at these two calls to String.Format();

String.Format("Name: {0}", "Justin");
String.Format("Name: {0} Occupation: {1}", "Justin", "Developer");

It may look like I’m using two overloads, but I am not. One might also conclude that String.Format() takes one string and eleventy billion object parameters all of which have a default of null. However, parameter defaults are new in .NET 3.5, and even the long forgotten .NET Framework 1.0 had a fully functional String.Format(). In actuality, the signature for String.Format() is as follows:

public static string Format(string format, params Object[] args)

Basically, the params keyword lets the user pass a variable size list of parameters, and populates them in a singe argument as an array. So now that we know about params, it seems like it shouldn’t be too hard to write our function lets give it a go.

private static void RaisError(SqlConnection cn, string message, short severity, short state, params object[] args) { . . . }

Now of course you want to protect against SQL injection. The best way to do that is to use the SqlCommand.Parameters collection to scrub the parameters. While you may be very clever, the ADO.NET code is very well battle tested. The only problem is if you don’t know how many parameters you are passing, how do you know how to make the string. The answer, is to generate SqlCommand.CommandText dynamically.

This may seem like you are opening yourself up for SQL injection. However, you aren’t. You simple have to generate a string that contains @arg1, @arg2, @arg3 . . . based on the length of the object array that gets passed to params. You then populate the SqlCommand.Parameters collection with the values of that array. If you don’t feel comfortable with this, then perhaps a stackoverflow answer with 268 votes at the time I wrote this article advocating this practice would convince you.

So putting it all together here is our function:

The above function is copyright 2012 myself (Justin Dearing). However, I release it under the MIT license, so feel free to use it in any of your code.