How to query if parameter is null ( having multiples parameters and select statement changes when on-CodePudding

I am creating a SSRS report based on a SQL table which has multiples columns. In my report I have up to 10 parameters. I want to create a dataset that will populate my report base on the parameters (each parameter being a condition in the query). The thing is that all my parameters except one can be NULL. Based on some research, I figured out that "if nested" can work, basically having something like this:

if(@param1 IS NOT NULL)
{
   SELECT * from table
   WHERE Column_1=@param1  ;
}
else
if(@param1 IS NOT NULL AND @param2 IS NOT NULL)
{
  SELECT * from table where Colum_1=@param1 AND Column_2=@param2;
}
   else.....

But I quickly realize that since I have 10 params that is a lot of combinations (IF) to have if I want to cover all the cases. For example if I consider that 3 of the params are not null then I will have to add a IF for the case where (param1, param2, param3) are not null, another one when (param1, param2, param4) are not null, (param1, param2, param5), (param1, param2, param6).... Same when 4 params are not null, 5 params are not null all the way up to when 10 params are not null.

Any ideas on a better ways and less time consuming to build this query?

Thanks!

CodePudding user response：

You can do this quite simply like this.

SELECT * FROM myTable 
    WHERE (Col1 = @p1 OR @p1 IS NULL)
      AND (Col2 = @p2 OR @p2 IS NULL)
      AND (Col3 = @p3 OR @p3 IS NULL)

and so on...