Avoiding branching in stored procedures that return search results?

Question

I have an application that needs to return search results from a SQL Server 2008 database. I would like to use a single stored procedure to return the results but I am finding that as I build the stored procedure it is full of many Else .. Else If statements with the query repeated over and over with slight variations depending on the users search criteria.

Is there a better way to go about this? I am trying to avoid writing dynamic SQL because I would like the benefits of an execution plan but I am thinking there must be a better way. Does anyone have any suggestions or perhaps examples of how best to design a stored procedure that has to deal with many search parameters, many of which may be NULL? Thank you.

Answer 1

I always run into this problem myself. Tend to use dynamic SQL, as long as you use the sp_executesql then the optimizer will try to use the same execution plan.

http://ayyanar.blogspot.com/2007/11/performance-difference-between-exec-and.html

Answer 2

I've always done this by using default values and conditions; e.g.

CREATE PROCEDURE [dbo].[searchForElement]
(
    @Town     nvarchar(100) = '',
    @County   nvarchar(100) = '',
    @postcode nvarchar(100) = ''
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT <fields> 

    FROM table 
    WHERE 
        (@Town = ''             OR Town     LIKE '%'+@Town+'%')
        AND (@County = ''       OR County   LIKE '%'+@County+'%')
        AND (@postcode = ''     OR postcode LIKE '%'+@PostCode +'%')
END

Edit:

As @gbn correctly advises the above will result in an index scan which may be a problem for large tables. If this is a problem the solution is to below using ISNULL and the fact that adding NULL to anything results in NULL it will allow an index seek because the '%' is understood by the optimiser (tested on SQL2008). This may be less readable but it makes better use of the indexes.

CREATE PROCEDURE [dbo].[searchForElement]
(
    @Town     nvarchar(100) = NULL,
    @County   nvarchar(100) = NULL,
    @postcode nvarchar(100) = NULL
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT <fields> 

    FROM table 
    WHERE   Town     LIKE ISNULL('%'+@Town+'%', '%')
       AND  County   LIKE ISNULL('%'+@County+'%', '%')
       AND  Postcode LIKE ISNULL('%'+@PostCode +'%', '%')
END

Answer 3

Not really.

With SQL Server 2005 and above with statement level recompilation then there is less of a penalty with OR clauses, just maintenance complexity.

Using Richard Harrison's approach makes it worse because OR is not-sargable, runs slowly, most likely won't use indexes.

Dynamic SQL opens up SQL injection, quoting and caching issues.

This leaves sp_executesql as per CountZero's answer which still requires building up strings.

The solution may not be code based... do you really need to search on all fields at any one time? I'd try to split into simple and advanced searches, or work out what the most common are and try to cover these queries.