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

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:

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.

  • Thank you. I will see the docs. I'm avoinding the usage of indexes because I'm not sure if they can really help or disturb the "inner join" operations. The main table has almost 120 fields and, at least, 60 of them are related to other tables. So, I'm a little bit worried with so many indexes to be created (and kept by MS-SQL engine) in an online system. – David BS Dec 29 '18 at 17:19
  • 3
    @DavidBS If your tables are that wide, take a look at this post: Do You Have Tables In Your Tables?. It might be time to normalize a bit. – Erik Darling Dec 29 '18 at 17:49
  • 2
    As a rule, indices on join columns will be extremely beneficial to query performance. its also not clear what problem you are trying to solve - your title suggests performance, your question suggests correctness. You might also consider using an ORM that will write queries for you.. e.g. github.com/ServiceStack/ServiceStack.OrmLite – kevinc Dec 29 '18 at 21:10
  • @kevinc, my goal it to get performance and, as shown by Erik above, the code can be better if following some code standards to do it. Anyway, thank you for recommendation - I'll see it tomorrow. – David BS Dec 29 '18 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.