Dynamic Search Conditions in T-SQL

Using the CLR

SQL 2005 adds the possibility to write stored procedures in languages that use the CLR (Common Language Runtime), such as Visual Basic .Net or C#. A dynamic search can be implemented in the CLR just as well as in T-SQL. After all, search_orders_1 is all about string manipulation until it invokes sp_executesql.

If you are more comfortable working in VB or C#, you could just as well implement your searches in the CLR rather than T-SQL. The reverse also applies: if you feel more comfortable with T-SQL, there is little reason to use the CLR to implement dynamic searches.

I've written two CLR procedures, search_orders_vb and search_orders_cs, that I will discuss in this section. As the code is fairly repetitive, I'm not including any of them in full here, but I only highlight some important points. Beware, that I will not go into any details on writing CLR stored procedures as such. If you have never worked with the CLR before, but is curious, I refer you to Books Online. At the end of this section there are instructions on how to create these two procedures in SQL Server.

Setting up the Statement

This is how search_orders_cs starts off:

string Query;
SqlCommand Command = new SqlCommand();

Query = @"SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
                 c.CustomerID, c.CompanyName, c.Address, c.City,
                 c.Region, c.PostalCode, c.Country, c.Phone,
                 p.ProductID, p.ProductName, p.UnitsInStock,
                 p.UnitsOnOrder
          FROM   dbo.Orders o
          JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID
          JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID
          JOIN   dbo.Products p ON p.ProductID = od.ProductID
          WHERE  1 = 1 ";

As you can see this is very similar to search_orders_1, including the dbo prefix. The rule that you should use two-part notation to maximise query-plan reuse applies to CLR procedures as well.

Defining the Parameters

This is the very important part. Far too often on the newsgroups, I see posters who interpolate the parameter values into the query string. This is bad for several reasons. One is that you get very little chance for query-plan reuse. The second and even more important reason is a security risk known as SQL injection, which I discuss in Curses and Blessings...

What you should do is to build parameterised statements. Here is how the @custid parameter is added in search_orders_cs:

if (! Custid.IsNull) {
   Query += " AND o.CustomerID = @custid" +
            " AND c.CustomerID = @custid";
   Command.Parameters.Add("@custid", SqlDbType.NChar, 5);
   Command.Parameters["@custid"].Value = Custid;
   Command.Parameters["@custid"].Direction = ParameterDirection.Input;
}

As in the T-SQL example, the query string is extended with the conditions for the parameter in both Orders and Customers.

What is different from T-SQL is how we define the parameter list and supply the value. In T-SQL the parameter list is a string, which includes all possible parameters. When working with the CLR, we only define the parameters that actually are in use. We define a parameter by adding it to the Parameters collection of the Command object. There are a number of ways to do this, and I refer you MSDN Library for a complete reference. The example shows a pattern that works for the most commonly used data types. The first parameter is the variable name, while the second parameter is the type indicator from the SqlDbType enumeration. The last parameter is the length, which you need to specify for the char, varchar, nchar, nvarchar, binary and varbinary data types, but you would leave it out for fixed-length types. Note that for decimal/numeric parameters, you need to use some different way to add them, as this flavour does not have means to specify scale and precision.

Once the parameter is defined, I assign the value separately. I also explicitly specify the direction, although this is hardly necessary.

Here is the above in Visual Basic .Net:

If Not Custid.IsNull Then
   Query &= " AND o.CustomerID = @custid" & _
            " AND c.CustomerID = @custid" & VbCrLf
   Command.Parameters.Add("@custid", SqlDbType.NChar, 5)
   Command.Parameters("@custid").Value = Custid
   Command.Parameters("@custid").Direction = ParameterDirection.Input
End If

It's very similar to the C# example. Different operator for string concatenation, parentheses to address elements in the collection and no semicolons.

Running the Query

This how this looks like in C#:

using (SqlConnection Connection =
   new SqlConnection("context connection=true"))
{
  Connection.Open();

  if (Debug) {
     SqlContext.Pipe.Send(Query);
  }

  Command.CommandType = CommandType.Text;
  Command.CommandText = Query;
  Command.Connection  = Connection;
  SqlContext.Pipe.ExecuteAndSend(Command);
}

Which very much is standard how you run a query from a CLR procedure. SqlContext.Pipe.Send is how you say PRINT in the CLR.

Loading the Examples

If you have any flavour of Visual Studio 2005 (including the Express editions), you can deploy search_orders_cs and search_orders_vb from Visual Studio. (But please don't ask me how to do it, Visual Studio just leaves me in a maze.)

Since the .Net Framework comes with SQL Server and includes compilers for the most common .Net languages, you can also load them without Visual Studio. First make sure that C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 (or corresponding) is in your path. Then run from a command-line window:

csc /target:library search_orders_cs.cs
vbc /target:library search_orders_vb.vb

To load the DLLs into SQL Server, you can use load_clr_sp.sql. You will have to change path in the CREATE ASSEMBLY command to where you placed the DLLs. Note that paths are as seen from SQL Server, so if you don't have SQL Server on your local machine, you will have to copy the DLLs to the SQL Server box, or specify a UNC path to your machine.

Trying it out

The same test cases as for search_orders_1:

EXEC search_orders_cs @orderid = 11000
EXEC search_orders_cs @custid  = 'ALFKI'
EXEC search_orders_cs @prodid  = 76
EXEC search_orders_cs @prodid  = 76, @custid = 'RATTC'
EXEC search_orders_cs @fromdate = '19980205', @todate = '19980209'
EXEC search_orders_cs @city = 'Bräcke', @prodid = 76

The query plans should be identical to search_orders_1, as it is the same queries. Hint: if you want to look at the query plans, you need use Profiler, and get the Showplan XML event. For some reason, you don't see query plans for queries submitted from CLR code in Management Studio or Query Analyzer.