Dynamic Search Conditions in T-SQL

While using IF for each and every combination is not feasible, you could still use IF statements to single out the most important search cases where search columns are indexed and for which users expect snap response. After all, a condition like c.Country = @country OR @country IS NULL is fairly harmless, because there is no index on Customers.Country anyway.

To demonstrate this technique, I wrote search_orders_4, which I'm including only abbreviated form. In this procedure, I have three different code paths: one if @orderid has been given, one if @custid is present and one none of them are provided:

IF @orderid IS NOT NULL
BEGIN
   SELECT ...
   WHERE  o.OrderID = @orderid
     AND  od.OrderID = @orderid
     AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
     AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
     AND  (od.ProductID = @prodid OR @prodid IS NULL)
     AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
   ORDER  BY o.OrderID
END
ELSE IF @custid IS NOT NULL
BEGIN
   SELECT ...
   WHERE  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
     AND  (o.OrderDate <= @todate OR @todate IS NULL)
     AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
     AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
     AND  c.CustomerID = @custid
     AND  o.CustomerID = @custid
     AND  (od.ProductID = @prodid OR @prodid IS NULL)
     AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
   ORDER  BY o.OrderID
END
ELSE
BEGIN
   SELECT ...
   WHERE  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
     AND  (o.OrderDate <= @todate OR @todate IS NULL)
     AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
     AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
     AND  (c.CompanyName LIKE @custname + '%' OR @custname IS NULL)
     AND  (c.City = @city OR @city IS NULL)
     AND  (c.Region = @region OR @region IS NULL)
     AND  (c.Country = @country OR @country IS NULL)
     AND  (od.ProductID = @prodid OR @prodid IS NULL)
     AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
   ORDER  BY o.OrderID
END

You can see that I have removed some of the conditions that are redundant if an order id or a customer id is given. You can also see that use the "double feature" of specifying the conditions on @orderid and @custid, in case this helps the optimizer.

In this example, I chose to give preference to @orderid and @custid, as they appeared to be the most important searches. Users certainly expect immediate response when they enter an order id, and often they need to see orders for a certain customer. On the other hand, a search on price is not likely to be common.

Could I have composed paths for all indexed columns? Nah, Northwind appears to be a bit over-indexed. In fact there are only two search columns in our case study that are not indexed: Customers.Country and Order Details.UnitPrice. As you realise, the complexity of the IF statements raises rapidly as you involve more conditions. For instance, if I also want to have separate branches for OrderDate, I would have to say something like:

ELSE IF @custid IS NOT NULL AND
        (@fromdate IS NOT NULL OR @todate IS NOT NULL)
     ...
ELSE IF (@fromdate IS NOT NULL OR @todate IS NOT NULL)
     ...
ELSE IF @custid IS NOT NULL

That is, I would need two extra branches. Without the first condition on all search parameters, the user who enters a customer with a small amount of orders, and also enters a meaningless @todate in the future would end up in the wrong branch. With the first branch, the optimizer can make the decision (provided that there is a WITH RECOMPILE somewhere).

Using Subprocedures

There is a problem with search_orders_4. If you first call it with a @orderid, and then with a @custid, the cached plan for the branch with the search on CustomerID will be based the NULL value passed the first time, which may not be the best plan.

You can circumvent this by forcing recompilation, but if you have many IF branches in the procedure, each branch is recompiled every time – when you only need one to be recompiled. And recompiling a lengthy procedure can be costly.

On SQL 2005, you can address this by adding OPTION (RECOMPILE) after each SELECT statement, so that only the SELECT statement for the search at hand gets recompiled.

Another solution is to split up the different SELECT statements in subprocedures, a suggestion I originally got from SQL Server MVP Simon Sabin. You can see this in search_orders_4a, which very abbreviated runs as:

   IF @orderid IS NOT NULL
   BEGIN
      EXEC search_orders_4a_sub1 @orderid   = @orderid,
                                 ...
   END
   ELSE IF @custid IS NOT NULL
   BEGIN
      EXEC search_orders_4a_sub2 @fromdate  = @fromdate,
                                 ...
                                 @custid    = @custid,
                                 ...
   END
   ELSE
   BEGIN
      EXEC search_orders_4a_sub3 @fromdate  = @fromdate,
                                 ...

Each subprocedure has its own plan in the cache, and for search_orders_4a_sub1 and sub2 that is a plan that is based on good input values from the first call. The catch-all search_orders_4a_sub3, still has a WITH RECOMPILE at it serves a mix of conditions.

The apparent downside is that the logic is scattered over several procedures, making maintenance more difficult.