Блог

Главная - Вопросы по программированию - Как вернуть все записи, если параметр равен null

Как вернуть все записи, если параметр равен null

#sql-server

#sql-сервер

Вопрос:

Ниже приведен мой SP: 

 Alter PROCEDURE GetList 
(

@FromDate date = null,
@ToDate date = null

)
AS

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

Если не было передано фильтра по дате, я хочу получить все записи.

Как бы я это сделал?

Ответ №1:

Вы можете сделать это:

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

Это также оставляет вас открытым для возможности оставить одну из дат нулевой, а другую — нет.

Комментарии:

1. Остерегайтесь запросов с перегрузкой ИЛИ — они имеют тенденцию кусаться.

Ответ №2:

вы бы сделали следующее

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

Комментарии:

1. по сути, мы говорим, что если параметр равен null, игнорируйте его. В противном случае он должен соответствовать критериям, следующим за ИЛИ

Ответ №3:

Пара жизнеспособных вариантов:

Вы могли бы установить, что @FromDate и @ToDate равны очень ранней или очень поздней дате, соответственно, они равны NULL.

Вы могли бы использовать sp_executesql и создать динамическую строку запроса с параметрами по мере необходимости, например

 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

Этот последний подход работает лучше, чем повсеместное использование ORS, поскольку запросы, включающие ORS, неизменно в конечном итоге очень плохо оптимизируются — они могут хорошо работать для определенного набора параметров, но, как правило, не являются универсальными.

Комментарии:

1. 1 за правильное построение строки SQL с параметрами в ней, а затем с использованием sp_executesql — Так много людей просто вводят значения и игнорируют последствия этого.

2. Голосую за то, чтобы подчеркнуть важность отказа от введения значений. 🙂

3. Хотя я думаю, что у вас есть правильная методология с динамическим SQL, это подход с использованием кувалды, когда все, что вам нужно, это молоток. Динамического SQL следует избегать, насколько это возможно.

4. @N8 — True — это приводит к сложности в обслуживании кода, для этого простого запроса, возможно, это излишне. Если CallList содержит миллионы строк, это может стоить того — несколько тысяч, тогда, вероятно, нет. Для хранимых процедур (обычно поисковых запросов), которые принимают множество параметров, которые могут иметь значение NULL, а могут и не иметь, динамический SQL часто спасает жизнь.

5. хех, о, я тоже НЕНАВИЖУ подобные процедуры! Я согласен с вами, хотя с 10-м параметром управлять этим становится просто кошмаром. самое сложное, с чем можно справиться, — это чертово перехват параметров!

Ответ №4:

Попробуйте следующее:

 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 )

Кроме того, если вы искали пересечение между двумя периодами, не забудьте выбрать более поздний FromDate и более ранний ToDate.

Ответ №5:

Динамический sql дополняется каждый раз, поэтому не используйте динамический sql

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

Ответ №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)

Ответ №7:

Вы также могли бы использовать BETWEEN (сокращенное обозначение меньше или равно и больше или равно):

 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))

Комментарии:

1. Если возможно, предоставьте некоторую дополнительную информацию (кроме просто SQL), чтобы объяснить, почему это лучше / отличается от других ответов, спасибо.

2. Между сокращениями LTE и GTE, между переводом в GTE и LTE во время компиляции запроса, поэтому я думаю, что время выполнения должно быть одинаковым, я просто даю краткий ответ на это