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