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
