Sometimes, dynamic queries are the way to go. But in this example would be interesting to see performance of the modified static query. It would look something like this:
SELECT [Name],
[Color],
[ListPrice],
[ModifiedDate]
FROM [Production].[Product]
WHERE [Name] LIKE coalesce(@Name, [Name])
AND Colour] LIKE coalesce(@Color, [Colour] )
AND [ListPrice] >= coalesce(@ListPriceMin, [ListPrice])
AND [ListPrice] <= coalesce(@ListPriceMax, [ListPrice])
AND ModifiedDate >= coalesce(@ModifiedDateMin, ModifiedDate)
AND ModifiedDate <= coalesce(@ModifiedDateMax, ModifiedDate)
I must say that I am not completely sure that this query is faster than dynamic sql, but seems like it should be.
BR