CODEWITHSUNDEEP
sql-server
Ezi
Ezi

Reputation: 2210

How to return all records if parameter is null

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?

Upvotes: 8

Views: 30087

Answers (7)

user7405604
user7405604

Reputation: 3

You could also use BETWEEN (short hand for less than or equals to and greater than or equals to):

declare @ToDate varchar(12)=null,
    @FromDate varchar(12)=null

select * from calllist As c where c.CallDate between 
CONVERT(datetime, isnull(@ToDate,c.CallDate)) 
and CONVERT(datetime,isnull(@FromDate,c.CallDate))

Upvotes: 0

Patrick
Patrick

Reputation: 1

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)

Upvotes: 0

Sai Sherlekar
Sai Sherlekar

Reputation: 1844

Dynamic sql is complie every time, so prevent 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)

Upvotes: 2

Akram Shahda
Akram Shahda

Reputation: 14771

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 the intersection between the two periods, do not forget to select the later FromDate and the earlier ToDate.

Upvotes: 2

Will A
Will A

Reputation: 24988

A couple of viable options:

You could set @FromDate and @ToDate to be equal to a very early or very late date respectively they're NULL.

You could use sp_executesql and construct a dynamic query string w/ parameters as needed e.g.

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 performs better than using ORs all over the place, as queries that include ORs invariably end up getting optimized very badly - they might work well for a certain set of parameters but are generally not one-size-fits-all.

Upvotes: 3

Colin Mackay
Colin Mackay

Reputation: 19175

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.

Upvotes: 16

Nathan Tregillus
Nathan Tregillus

Reputation: 6334

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)

Upvotes: 6

Related Questions