Dynamic filtering with SQL — Mechanics of software

The one of the most frequent asked question in database development is "How to filter the data returned by a query according to some user selected criteria?". To complete the solution with dynamic ordering see my other blog post.

For every method template I will add icons indicating corresponding advantages and drawbacks.

good query plan (uses indexes)

bad query plan for big tables (table scan)

query is recompiled every time

works only with NOT NULL columns

I use Microsoft demo database AdventureWorks for the tests. A test query should select persons by their first and last names.

USE AdventureWorks
GO
CREATE PROCEDURE Person.Person_GetByNames
   @LastName  nvarchar(50) = NULL,
   @FirstName nvarchar(50) = NULL
AS
BEGIN
  -- !! Add your code here !!
END
GO

To run the tests:

-- Filtered records only
EXEC Person.Person_GetByNames @LastName = 'Bacon', @FirstName = DEFAULT;
-- All records
EXEC Person.Person_GetByNames @LastName = DEFAULT, @FirstName = DEFAULT;

Now we'll replace "Add your code here" by different dynamic filtering methods.

Dynamic SQL

Create a WHERE condition dynamically according to specified parameters. Be aware that user should have the SELECT permission on the table.

DECLARE
   @SQL nvarchar(max) = '',
   @Params nvarchar(max) = '@LastName nvarchar(50), @FirstName nvarchar(50)';
IF @LastName IS NOT NULL
   SET @SQL = @SQL + ' LastName = @LastName AND ';
IF @FirstName IS NOT NULL
   SET @SQL = @SQL + ' FirstName = @FirstName AND ';
IF @SQL <> ''
   SET @SQL = 'WHERE ' + substring(@SQL, 1, len(@SQL) - 4);
SET @SQL = 'SELECT * FROM Person.Person ' + @SQL;
EXEC sp_executesql @SQL, @Params, @LastName, @FirstName

Use IF..ELSE

IF @LastName IS NULL AND @FirstName IS NULL
   SELECT * FROM Person.Person
ELSE IF @LastName IS NOT NULL AND @FirstName IS NULL
   SELECT * FROM Person.Person
   WHERE LastName = @LastName
ELSE IF @LastName IS NULL AND @FirstName IS NOT NULL
   SELECT * FROM Person.Person
   WHERE FirstName = @FirstName
ELSE
   SELECT * FROM Person.Person
   WHERE LastName = @LastName AND FirstName = @FirstName

Use OR

SELECT * FROM Person.Person
WHERE
   (LastName = @LastName OR @LastName IS NULL) AND
   (FirstName = @FirstName OR @FirstName IS NULL)

Use CASE expression

There is no big difference with previous "Use OR" method, just an another way to write SQL.

SELECT * FROM Person.Person
WHERE
   CASE
      WHEN @LastName IS NULL THEN 1
      ELSE CASE WHEN LastName = @LastName THEN 1 ELSE 0 END
   END = 1
   AND
   CASE
      WHEN @FirstName IS NULL THEN 1
      ELSE CASE WHEN FirstName = @FirstName THEN 1 ELSE 0 END
   END = 1

Use coalesce() function

SELECT * FROM Person.Person
WHERE
   LastName = coalesce(@LastName, LastName) AND
   FirstName = coalesce(@FirstName, FirstName)

Particular case solution: form the columns NOT NULL of string type you may modify the expression.

SELECT * FROM Person.Person
WHERE
   LastName LIKE coalesce(@LastName, '%') AND
   FirstName LIKE coalesce(@FirstName, '%')

Use BETWEEN

You need to choose a good values for maximal and minimal constants according the data type.

DECLARE
   @MinValue nvarchar(1) = '',
   @MaxValue nvarchar(1024) = replicate(char(255), 1024);
SELECT * FROM Person.Person
WHERE
   LastName BETWEEN coalesce(@LastName, @MinValue)
      AND coalesce(@LastName, @MaxValue)
   AND
   FirstName BETWEEN coalesce(@FirstName, @MinValue)
      AND coalesce(@FirstName, @MaxValue)

Use UNION ALL

The method is similar to "Use IF..ELSE" one but may be a more hard to optimize for SELECT * case or if there is no index on filtered columns.

SELECT * FROM Person.Person
WHERE
   LastName = @LastName AND @LastName IS NOT NULL
   AND
   FirstName = @FirstName AND @FirstName IS NOT NULL
UNION ALL
SELECT * FROM Person.Person
WHERE
   LastName = @LastName
   AND @LastName IS NOT NULL
   AND @FirstName IS NULL
UNION ALL
-- If no index on FirstName ==> may introduce a bad plan
SELECT * FROM Person.Person
WHERE
   FirstName = @FirstName
   AND @FirstName IS NOT NULL
   AND @LastName IS NULL
-- Will always scan table
UNION ALL
SELECT * FROM Person.Person
WHERE @FirstName IS NULL AND @LastName IS NULL

Solution: combine this method with "Use IF..ELSE" one.

IF @FirstName IS NULL AND @LastName IS NULL
   SELECT * FROM Person.Person
ELSE
   SELECT * FROM Person.Person
   WHERE
      LastName = @LastName AND @LastName IS NOT NULL
      AND
      FirstName = @FirstName AND @FirstName IS NOT NULL
   UNION ALL
   SELECT * FROM Person.Person
   WHERE
      LastName = @LastName
      AND @LastName IS NOT NULL
      AND @FirstName IS NULL
   UNION ALL
   SELECT * FROM Person.Person
   WHERE
      FirstName = @FirstName
      AND @FirstName IS NOT NULL
      AND @LastName IS NULL