How would you do this - Solution
In the past, I've come up with some pretty nutty ways of handling this, most of which involved some version of COALESCING things - or, back in the day, Dynamic Sql. However my homie/co-worker Jason Burton showed me the light on this one:
CREATE PROCEDURE [DBO].[usp_somesamplesearchprocedure]
@FirstName VARCHAR(8) = NULL,
@LastName VARCHAR(20 = NULL,
@CustomerID VARCHAR(20) = NULL,
@DateOfBirth SMALLDATETIME = NULL
AS
SELECT *
FROM MyTableName
WHERE
(txt_First_Name LIKE @FirstName + '%' OR @FirstName IS NULL)
AND (txt_Last_Name LIKE @LastName + '%' OR @LastName IS NULL)
AND (n_CustomerID = @CustomerID OR @CustomerID IS NULL)
AND (dte_DateOfBirth = @DateOfBirth OR @DateOfBirth IS NULL)
By specifying a default value - you can just go ahead and pass in a search value from the client code if you want to used, or just pass in nothing if you don't. From the client side, you can (tbFirstName.Text != string.Empty)? tbFirstName.Text : string.Empty; (You'll need to do some conversion in your API or DALC implementation to take the String.Empty and convert it into a DbNull.Value but that's hardly worth mentioning. We used Like in FirstName and LastName so that the requirement of similar matched names would be met, nothing too fancy about this. The cool part is the parameter usage. If there's a value, we restrict the columns based on each respective search criteria value. If there isn't, we don't want to restrict on the field at all. So if we passed in null for @FirstName (or didn't specify it), the value would be null so @FirstName IS Null would evaluate to true for each record. The same logic holds for the other fields. There are no non-deterministic functions being used here, so indexes can be used and the execution of the query will happen quite efficiently.
AFAIK, this is one of the better ways to handle this situation - but I'm not implying this is the only one. Would definitely love to hear some feedback (either why they like this way or why another way would be better) on this issue.