Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
478 views
in Technique[技术] by (71.8m points)

sql server - 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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...