COALESCE - Dynamic where clause

Syntax

COALESCE ( expression [ ,...n ] )
Arguments expression
Is an expression of any type.
n

To search data when there is no of parameter is not fix. i.e Dynamic search fields. Returns the first nonnull expression among its arguments. Consider case when you want to put search on each field of your table

Example:
Table structure

Suppose you have requirement to search data from the table with each field and you don’t have to consider field in search when the value is blank or null.

So to achieve this task first thing you do is take variable for each field and check the value of the variable weather its blank or null and then build where condition for the query something as below.

But thing is to simple with the COALESCE
Advantages 

• Eliminate to check the value for each variable your defined 
• There is no need of exec procedure to execute value 
• Execution plan get stored by sql server because it's not a dynamic query. 

Summary 
So this kind of function more help full when your are building dynamic search functionality or building reports with dynamic search fields of table.