Sometimes you will display search mask to your user. The user will then enter search conditions. You will return a list of all records matching them; unless conditions were left empty in which case you will ignore them.
Today's article deals with the SQL part of this problem.
One approach that I've seen many times is to generate SQL on the fly and use sp_executesql to execute it. But this should really be avoided because it breaks SQL Server's chained security.
Much more elegant solution is to use simple or clause in the where statement. Following code demonstrates how to do it.
1 CREATE PROCEDURE ListPersons
2 @Name AS VARCHAR(30) = NULL
3 ,@DOBFrom AS DATETIME = NULL
4 ,@DOBTo AS DATETIME = NULL
11 Person P
13 (P.Name LIKE ‘%’+ @Name +’%’ OR @Name IS NULL)
14 AND (P.DOB >= @DOBFrom OR @DOBFrom IS NULL)
15 AND (P.DOB <= @DOBTo OR @DOBTo IS NULL)
There. Calling ListPersons without parameters will return all persons. Calling ListPerson with only DOBFrom parameter will return all persons that have date of birth larger or equal to this parameter. Any additional parameter will narrow down your search.