How to return all records if the parameter is zero

Question

Below 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 filter of past dates, I want to get all records.

How can I do it?

Answer 1

Several viable options:

You can set @FromDate and @ToDate to the earliest or very latest date, respectively, they are NULL.

You can use sp_executesql and build a dynamic w / parameters query string as needed, for example.

 DECLARE @Sql NVARCHAR(MAX) = 'SELECT * FROM CallList C WHERE 1 = 1 ' IF @FromDate IS NOT NULL BEGIN SET @Sql += ' AND C.CallDate > @xFromDate' END IF @ToDate IS NOT NULL BEGIN SET @Sql += ' AND C.CallDate < @xToDate' END EXEC sp_executesql @Sql, N'@xFromDate DATETIME, @xToDate DATETIME', @xFromDate = @FromDate, @xToDate = @ToDate 

This latter approach works better than using ORs everywhere, since queries that include ORs invariably end up being optimized very poorly - they may work well for a specific set of parameters, but usually are not suitable for the same size.

Answer 2

You can do it:

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

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

Answer 3

do the following

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

Answer 4

Try the following:

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

---

Also, if you were looking for an intersection between two periods, be sure to select a later FromDate and a previous ToDate.

Answer 5

Dynamic sql is executed every time, therefore it is not recommended to use dynamic sql

  select * from calllist As c where (c.CallDate < @ToDate or @ToDate is null) and (c.CallDate > @FromDate or @FromDate is null) 

Answer 6

 DECLARE @BgnDate date, @EndDate date SELECT @BgnDate = MIN(c.CallDate), @EndDate = MIN(c.CallDate) FROM CallList Select * FROM CallList c Where c.CallDate > ISNULL(@FromDate,@BgnDate) and c.CallDate < ISNULL(@ToDate,@EndDate)