I was recently surprised to find that, for dynamic Where clauses, the dynamic SQL performed better and made better use of indexes. This was pointed out to me by a co-worker. I was suggesting the use of coalesce or something like @Cus_Name IS NULL OR Cus_Name = @Cus_Name in the Where clause.
Neither one of these solutions (COALESCE or (@A is NULL or A=@A)) were good solutions for this type of search on tables of this size. We did tests with all 3 methods. The second method is significantly faster than the first (5-10 seconds instead of minutes), but a dynamic SQL with only the where clauses you need is much faster (subsecond).
It appears that the best solution for our test was to build Dynamic SQL strings with the parameters you need. Its more work but it’s the fastest solution. Oh, and make sure they are parameterized SQL, not SQL with hardcoded values (using sp_executesql with @params), that way there can be some plan reuse.