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.