Dtyjlui

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.

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.