Passing null values while using between in a query

Question

I'm writing a stored procedure in T-SQL to select records from the following table:

CREATE TABLE [dbo].[PHM_News_Entry]
(
    [NewsID] [int] IDENTITY(1,1) NOT NULL,
    [NewsTitle] [varchar](200) NULL,
    [NewsDate] [datetime] NULL,
    [NewsDescription] [nvarchar](max) NULL,
    [NewsDateEntry] [datetime] NULL,
    [NewAuthor] [varchar](200) NULL,
    [NewModificationDate] [datetime] NULL,
    [NewModifiedBy] [varchar](100) NULL,
    [Status] [varchar](50) NULL,
    [NewsAttatchmentPath] [nvarchar](max) NULL,
    [NewsImagePath] [nvarchar](max) NULL,
)

The stored procedure is as follows:

CREATE PROCEDURE Select_News 
    @NewsTitle VARCHAR(200), 
    @FromDate DATE,
    @ToDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * 
    FROM PHM_News_Entry 
    WHERE (NewsTitle LIKE '%'+@NewsTitle+'%' OR @NewsTitle IS NULL) 
      AND NewsDate BETWEEN @FromDate AND @ToDate OR NewsDate IS NULL
END

The parameters viz., @NewsTitle, @FromDate and @ToDate can either have values or be NULL. If all 3 parameters or NULL, there should be no record selected but if one of them has non-null value, it should select one or more matching records.

If I pass NULL to all 3 parameters, it works as expected i.e., doesn't select any records but it's not working the other way i.e., if one of them one or two of them have non-null values.

Please help me write the correct query for this case.

Thanks and regards,

Shyam Singh

Answer 1

Use this WHERE clause:

WHERE (NewsTitle LIKE '%'+@NewsTitle+'%' OR @NewsTitle IS NULL) AND
      (NewsDate BETWEEN COALESCE(@FromDate, CAST('1753-01-01' AS DATE)) AND
                       COALESCE(@ToDate, CAST('9999-12-31' AS DATE)) OR NewsDate IS NULL)

Explanation:

COALESCE(@FromDate, CAST('1753-01-01' AS DATE))

This will use the value of @FromDate if it be not NULL, or it will use the smallest possible date in SQL Server if it is NULL. So when @FromDate is NULL, then this expression would allow all dates which are earlier than the @ToDate. Similar logic applies for the other COALESCE expression.