Following on from my last question http://stackoverflow.com/questions/2788082/sql-server-query-performance, and discovering that my method of allowing optional parameters in a search query is sub optimal, does anyone have guidelines on how to approach this?
For example, say I have an application table, a customer table and a contact details table, and I want to create an SP which allows searching on some, none or all of surname, homephone, mobile and app ID, I may use something like the following:
select *
from application a inner join customer c on a.customerid = a.id
left join contact hp on (c.id = hp.customerid and hp.contacttype = 'homephone')
left join contact mob on (c.id = mob.customerid and mob.contacttype = 'mobile')
where (a.ID = @ID or @ID is null)
and (c.Surname = @Surname or @Surname is null)
and (HP.phonenumber = @Homphone or @Homephone is null)
and (MOB.phonenumber = @Mobile or @Mobile is null)
The schema used above isn't real, and I wouldn't be using select * in a real world scenario, it is the construction of the where clause I am interested in. Is there a better approach, either dynamic sql or an alternative which can achieve the same result, without the need for many nested conditionals. Some SPs may have 10 - 15 criteria used in this way
