SQL Server: Filter by layers ... Need help or advise

Question

i am working with data base and based on my client's needs, he wants to be able to create reports from a web page, so what i am trying to do is a stored procedure with a query, then pull that result into a dataset and custom parsing it into an XML for further manipulation. But here is the catch, i have N parameters for filtering, so here is an example of what i am doing ATM:

@p_parameter1 AS varchar(20)
@p_parameter2 AS varchar(15)
@p_parameter3 AS datetime
@p_parameter4 AS datetime
@p_parameter5 AS int
@p_parameter6 AS char(10)

begin
   select table1.column1 'SomeName', ... , tableN.columnN 'SomeName'
   from table1 tb1
        join table2 tb2 on tb1.key = tb2.foreignkey
        .
        .
        .
        join tableN tbN on tbM.key = tbN.foreignkey
        left join tableO tbO on tb2.key = tbO.foreignkey
   where  tb1.somefield like '%'@p_parameter1'%'
          tb2.somefield like '%'@p_parameter2'%'
          tb3.somefield like '%'@p_parameter3'%'
          tb1.tr1_date between @p_parameter3 and @p_parameter4
end

So this is an example of what i am doing ATM, i am trying to filter this in a single query, but it still doesnt work the way i want it to, some of the filters are sometimes comparing with NULL fields, which brings no results, so i tried including the results even if one filter is NULL, but it brings everything ... I also tried making a temporary table in the first query and apply one filter, then to that temporary table make another query to apply another filter and so on ... But i can not make it work. I am pretty sure that there is a better way to do all this mess, but i am quite new to SQL queries with joins and such.

Thanks in advance.

EDIT 1: Also i forgot to mention that some of the filter parameters may not be sent, or sent as null.

Answer 1

You have to use IS NULL expression to check if parameters are nulls and if so do not filter by them. Try something like that:

where  
   (@p_parameter1 IS NULL OR tb1.somefield like @p_parameter1)
   AND
   (@p_parameter2 IS NULL OR tb2.somefield like @p_parameter2)
   AND
   ...