SSRS search multiple parameters allowing some blank

Question

I am looking to be able to search locations of orders using any of 4 parameters. What I have works when looking for a set pair of complete parameters but when any of them are left blank no results are returned. (all 4 parameters are declared as varchar(255))

select
  ContactName,
  ContactEmail,
  ContactPhone,
  OriginCity,
  OriginState,
  OriginZip,
  DestCity,
  DestState,
  DestZip
from Orders
where 
  (OriginCity in (@Origin)
  or OriginState in (@OriginS))
  and 
  (DestCity in (@Dest)
  or DestState in (@DestS))
order by 
  OriginCity,
  OriginState,
  DestCity,
  DestState

Answer 1

You need to add an additional OR condition in your WHERE clause to handle the NULL parameter. So your WHERE clause should look something like this:

WHERE 
 ( 
  (OriginCity in (@Origin) OR @Origin IS NULL )
     OR 
  (OriginState in (@OriginS) OR @OriginS IS NULL )
 )
 AND 
 (
  (DestCity in (@Dest) OR @Dest IS NULL )
    OR 
  (DestState in (@DestS) OR @DestS IS NULL )
 )

To avoid table scans, add OPTION(RECOMPILE) at the very end of the query.

Answer 2

You can try using dynamic sql for handling this. Refer to the link - http://www.sommarskog.se/dynamic_sql.html and have appropriate indexes for performance.