I need to write many SP that have many optional filtering parameters. After reading Turgay Sahtiyan's (Senior SQL Server Premier Field Engineer at Microsoft) blog he mentions exactly the same problem and the way to solve it is through dynamic SQL. Found it in his slides http://blogs.msdn.com/b/turgays/archive/2013/09/07/sqlsaturday-243-cape-town-top-10-performance-tips-for-sql-server-developers.aspx
So my questions is it the only and the best approach or are there other options?
WHERE (@EmployeeFirstName IS NULL OR emp.First_Name LIKE @EmployeeFirstName ) AND (@EmployeeLastName IS NULL OR emp.Last_Name LIKE @EmployeeLastName ) AND (@EmployeeNetworkLogOn IS NULL OR emp.Network_ID LIKE @EmployeeNetworkLogOn ) AND (@EmployeeStatus IS NULL OR emp.Employee_Status = @EmployeeStatus ) AND (@EmployeeType IS NULL OR emp.Employee_Type = @EmployeeType ) AND (@SupervisorFirstName IS NULL OR sup.First_Name LIKE @SupervisorFirstName ) AND (@SupervisorLastName IS NULL OR sup.Last_Name LIKE @SupervisorLastName ) AND (@SupervisorNetworkLogOn IS NULL OR sup.Network_ID LIKE @SupervisorNetworkLogOn)