In this Post Discus how to Search multiple criteria with single where clause in MS Sql
WHERE 1=(CASE WHEN @variable1 ='' THEN '1' ELSE CASE WHEN Dtl.chrShipmentNo=@variable1
THEN '1' ELSE '0' END END)
AND 1=(CASE WHEN @variable2 ='' THEN '1' ELSE CASE WHEN Dtl.varContainerNo LIKE '%'+
@variable2 + '%' THEN '1' ELSE '0' END END)
AND 1=(CASE WHEN @variable3 ='' THEN '1' ELSE CASE WHEN Dtl.varMasterInvoice
IN(@variable3) THEN '1' ELSE '0' END END)
How to Search multiple criteria with single where clause in MS Sql