MSSQL - Query efficiency to many filters

Question

I'm making an ASP.NET website which users can select more than 20 different filters at the same time. Something like 'Product Type', 'Year', 'State-of-Usage', 'Color', 'Size' and so forth.

Many of these items are related to other tables. So, my query makes a 'SELECT' using WHERE and 'INNER JOIN' to 12 other tables.

The problem resides in parameters that can be present or not. I mean, depending of the user choice, some parameters will be NULL or not and I can't utilize them in my query (like using ISNULL()) because I'm affraid of return a wrong result caused by this.

So, my solution is build the query string depending of the parameters received , I mean, the query will consider only those which have real values.

declare @SEARCH_QUERY nvarchar(max);

set @SEARCH_QUERY = 'select AcervoID, Categoria, Fabricante, Personagem, Estado, 
                        IIF(LEN(Description) > 120, LEFT(Description,120) + ''...'', Description) [mDescription],
                        Value, Availability, ActiveLink, Title,
                        RIGHT([thumb], CHARINDEX(''\'', REVERSE([thumb])) -1) [Image],
                        E.UsoID, E.FontAwesome
                        from Acervo A   
                        inner join Categorias C on C.CatID = A.Categoria
                        inner join Fabricantes F on F.FabID = A.Fabricante
                        inner join Personagem P on P.PersID = A.Personagem
                        inner join EstadoUso E on E.UsoID = A.Estado 
                        where A.Ativo = ''true'' and A.Disponiveis > 0'



  if @m_Cat >-1
    set @SEARCH_QUERY = @SEARCH_QUERY + ' and A.Categoria = ' + convert(nvarchar(4),@m_Cat)
  if @m_Fab >-1
    set @SEARCH_QUERY = @SEARCH_QUERY + ' and A.fabricante = ' + convert(nvarchar(4),@m_Fab)
  if @m_Pers > -1
    set @SEARCH_QUERY = @SEARCH_QUERY + ' and A.Personagem = ' + convert(nvarchar(4),@m_Pers)
  if @m_EU is not null
    set @SEARCH_QUERY = @SEARCH_QUERY + @m_EU



exec sp_executeSQL @SEARCH_QUERY

Is there a best way to do it?

Thanks in advance.

Answer 1

The basic form you have is correct, though you want to avoid concatenating parameters into the string the way you have. See:

• Revisiting catch-all queries

• #BackToBasics : An Updated "Kitchen Sink" Example

The efficiency part will depend ultimately on the supporting indexes you have for your queries, along with other local factors, like hardware, blocking, rows returned, etc.