Case expression for null value in where clause

Question

I have a SQL procedure with two parameters @DateFrom and @DateTo and SQL query inside.

For example

select *
from Produc p
where p.ProductType = 1 
and (ProductCategory = 5 or ProductCatalogId = 3)
or (p.StartDate between @DateFrom and @DateTo)

But I want a last or only when parameters @DateFrom and @DateTo are not null.

Answer 1

Try:

select *
from Product p
where (
  p.ProductType = 1
  and (ProductCategory = 5 or ProductCatalogId =3)
)
or (
  @DateFrom is not null and @DateTo is not null
  and p.StartDate between @DateFrom and @DateTo
)

Although I'm wondering if thats the logic you are looking for, maybe the following is what you actually want.

select *
from Product p
where p.ProductType = 1
and (ProductCategory = 5 or ProductCatalogId =3)
and (
  @DateFrom is null
  or @DateTo is null
  or p.StartDate between @DateFrom and @DateTo
)

Answer 2

It sounds like you want the parameters to be optional. So when a parameter is null you want to ignore it and don't apply its date range limit.

A typical way for optional parameters:

p.StartDate between coalesce(@DateFrom, p.StartDate) and coalesce(@DateTo, p.StartDate)

Another, longer, but more straight-forward way:

(p.StartDate >= @DateFrom or @DateFrom is null)
and
(p.StartDate <= @DateTo or @DateTo is null)