I had read your article and did some testing on a stored procedure designed to find a person from my persons table. The stored procedure had 3 nullable paramters, @person_id int, @last_name nvarchar(20), @first_name nvarchar(20). The query I used using case is:
Select P.person_id, P.last_name, P.first_name, P.middle_name, P.birth_date From dbo.persons P Where P.person_id = Case When @person_id Is Null Then P.person_id Else @person_id End And P.last_name Like Case When @last_name Is Null Then P.last_name Else @last_name + '%'End And P.first_name Like Case When @first_name Is Null Then P.first_name Else @first_name + '%' End
I then duplicated the stored procedure with one using dynamic sql to append the appropriate where clause based on the parameters and executed it using sp_executesql. Performance was much better based using the dynamic SQL. The SP using dynamic SQL used less CPU, ran faster, and took fewer reads in each scenario. Mainly because the optimizer had to use the non-clustered index on last_name, first_name in every case while the dynamic SQL was able to use the appropriate index. For example when passing in just @person_id the SP using Case had these stats:
Table 'Persons'. Scan count 1, logical reads 257, physical reads 5, read-ahead reads 251, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
While the SP with dynamic SQL had these stats:
Table 'Persons'. Scan count 0, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
These kind of performance differences make me lean toward dynamic SQL over Case in the Where clause when you have optional parameters.