How to use or ignore parameters in SQL stored procedure depending on value

Question

I have several drop-down lists with item-value pairs on front-end side. Let's say in the first ddl(animals) - dog has value 1, cat - 2, ANY - 0, in the second ddl(colors) - black color has value 1, white - 2 and ANY - 0.

Also I would like to use stored procedure to select the data from db by the values selected in drop-downs.

So, the result should be filtered only in case of value != 0. in case of 0 the result should not be filtered. The best solution what I can imagine is to create SELECT query on code side (w/o SP):

string animalParam = ddl_animal.SelectedValue.Equals("0") ? "1 = 1" : $" animal_id={ddl_animal.SelectedValue}";
string colorParam = ddl_color.SelectedValue.Equals("0") ? "AND 1 = 1" : $"AND color={ddl_color.SelectedValue}";

string query = $"SELECT * FROM table WHERE {animalParam} {colorParam}";

But is it good enough? Or there is a better way to do this e.g. on SP side? Using default values somehow? Thanks in advance!

Answer 1

Your solution should work, but you can also move the logic into SQL if you prefer to:

CREATE PROCEDURE dbo.FilterByColorAndAnimal
(
    @AnimalId INT = 0,
    @ColorId INT = 0
)
AS
BEGIN
    DECLARE @SQL = N'
        SELECT * 
        FROM table W
        WHERE 1 = 1'

    IF (@AnimalTypeId <> 0) SET @SQL = @SQL + ' AND animal_id = @AnimalId'
    IF (@ColorId <> 0) SET @SQL = @SQL + ' AND color_id = @ColorId'

    EXEC sp_executesql @SQL, '@AnimalId INT, @ColorId INT', @AnimalId, @ColorId
END
GO

Alternatives:

• do not use dynamic SQL, but a more convoluted query involving ORs

  SELECT * 
  FROM table W
  WHERE (@AnimalId = 0 OR animal_id = @AnimalId)
      AND (@ColorId = 0 OR color_id = @ColorId)
  

• use LINQ2SQL with EntityFramework

  var query = context.Table;
  int animalId = Convert.ToInt32(ddl_animal.SelectedValue);
  if (animalId != 0)
      query = query.Where(item => item.animal_id = animalId);
  int colorId = Convert.ToInt32(ddl_color.SelectedValue);
  if (colorId != 0)
      query = query.Where(item => item.color_id = colorId);
  

This will generate the most efficient query (similar to your initial solution).