How to return all records if parameter is null

Question

The following is my SP:

Alter PROCEDURE GetList 
(

@FromDate date = null,
@ToDate date = null

)
AS

Select * FROM CallList c
Where c.CallDate > @FromDate and c.CallDate < @ToDate 

If there was no passed date filter, I want to get all the records.

How would I do it?

Answer 1

You can do this:

SELECT * FROM CallList c
WHERE (c.CallDate > @FromDate OR @FromDate IS NULL) AND 
      (c.CallDate < @ToDate OR @ToDate IS NULL)

This also leaves you open to the possibility to leaving one of the dates null and not the other.

Answer 2

you'd do the following

SELECT * 
FROM CallList AS C
WHERE (@FromDate IS NULL OR c.CallDate > @FromDate)
AND (@ToDate IS NULL OR c.CallDate < @ToDate)