At the risk of sounding snotty, you handicapped Case 2 intentionally to remove it from consideration. Neither of the other cases will return the same RESULTS as case 2 will. Try it. Case 3 will return nothing because LastName and Email are Null. Case 4 will return nothing as @Email is NULL. Only Case 2 properly represents a FirstName search. Therefore it is the only correct case.
Look at the number of coded cases you will need for the approach in case 3...
1 input parameter = 2 cases (with/without null)
2 input parameters = 4 cases
3 input parameters = 8 cases
4 input parameters = 16 cases
5 input parameters = 32 cases
6 input parameters = 64 cases
7 input parameters = 128 cases
8 input parameters = 256 cases
Now, if you need a true dynamic query the ONLY option that has performance across all input parameters on a million row test table is Dynamic SQL. Otherwise at 8 input parameters you are trying to maintain 256 cases which is not only impractical, it's virtually impossible.
Edit: I didn't realize this article/thread was over 2 years old... I don't expect a response given how old it is.
James Dingle-651585 (5/26/2010)
If OBJECT_ID('Case3GetUser', 'P') Is Not Null Drop Procedure Case3GetUser;
Create Procedure Case3GetUser (
@LastNameVarChar(50) = Null,
@FirstNameVarChar(50) = Null,
@EmailVarChar(255) = Null
RaisError('Case3GetUser', 10, 1) With NoWait;
-- Last name only (omitted but implicit: "And @LastName Is Not Null")
UserId In (Select UserId From [User] Where @LastName = LastName And @FirstName Is Null)
-- Last name and first name together (omitted but implicit "And LastName Is Not Null And @FirstName Is Not Null")
Or UserId In (Select UserId From [User] Where @LastName = LastName And @FirstName = FirstName)
-- by email (omitted but implicit "And @Email Is Not Null")
Or UserId In (Select UserId From [User] Where @Email = Email)
Looking at the execution plans, we observe that:
1. Method 2 have a unique execution plan per combination of parameters, while methods 3 and 4 always have the same one.
2. Because methods 3 & 4 have each only ONE execution plan, it's easy to review. For method 2, you have to test all the combinations of parameters and look at each execution plan generated.
3. The only terrible SQL statement and execution plan come from method 2. It is an illustration of an unexpected code path leading to the execution of something that has not been reviewed nor predicted.
Looking at the I/O statistics you will observe that:
1. All methods give the same performance when running in the use case they were designed for.
3. In the bogus situation, method 2 is the only one to turn into a full scan, while others do not put the server in danger.