Dynamic filtering with SQL | Mechanics of software

Submitted by st on Mon, 15/11/2010 - 18:45

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

SQL Server