Optional search condition in SQL Server

Question

I am trying to create a query where when you do not input value in @date variable it will show result in SQL.

declare @Holiday varchar(50), @Date date
set @holiday = null
set @date = null

Select *
from tbl_Holiday
where Holiday like '%' + isnull(@Holiday,'') + '%'
and HDate = @Date

The above codes are the one I'm using. When I comment the last line of code it works fine, but I need to find a way to use the date for searching. Thank you for future responses.

MySQL does not use `TOP x` and/or `DECLARE`, please correct your question. (using [edit]) — Luuk, Jul 09 '21 at 06:28
Just set the `@holiday` value as `NULL` instead of `''`, because that's what the `ISNULL` will check for. — EzLo, Jul 09 '21 at 06:45
Your description is unclear, but from what I can understand, `... and HDate = isnull(@Date, HDate)` could work. — Arvo, Jul 09 '21 at 06:56

score 0 · Answer 1 · answered Jul 09 '21 at 06:56

There're a few ways to do it. I'd keep it simple:

declare @Holiday varchar(50), @Date integer
set @holiday = Null
set @Date = Null

IF @Date !='' or @Date is NOT NULL
    BEGIN
        Select *
        from tbl_Holiday
        where Holiday like '%' + isnull(@Holiday,'') + '%'
        and HDate = @Date
    END
ELSE 
BEGIN
        Select TOP (5)*
        from tbl_Holiday
END

score 0 · Answer 2 · answered Jul 09 '21 at 06:57

Use an OR to check for NULL for @Date.

where 
    (@Holiday IS NULL OR Holiday like '%' + @Holiday + '%') and 
    (@Date IS NULL OR HDate = @Date)

Whenever supplied @Date is NULL, condition will result to true and the other @Date filter won't apply.

Using this trick multiple times on the same query (multiple optional filters) will have impact on performance due to poor plan execution. If you need performance please read this thread about optimization.

score 0 · Answer 3 · answered Jul 09 '21 at 11:50

If you care about optimization (i.e. the use of indexes) then separate queries or dynamic SQL are probably the better choice.

But if you want to just express the logic in a single query, then I would recommend CONCAT() for the holiday piece and then an explicit NULL comparison:

Select *
from tbl_Holiday
where Holiday like concat('%', @Holiday, '%') and
      (@Date is null or HDate = @Date);

Unlike +, concat() simply ignores NULL values, which is convenient in this case.

score 0 · Answer 4 · answered Jul 09 '21 at 14:44

0

You can use ISNULL also with your @Date variable:

declare @Holiday varchar(50), @Date date
set @holiday = null
set @date = null

SELECT  *
FROM    tbl_Holiday
WHERE   Holiday like '%' + ISNULL(@Holiday,'') + '%'
AND     HDate = ISNULL(@Date, HDate)

answered Jul 09 '21 at 14:44

Yair Maron

1,860
9
22

Optional search condition in SQL Server

4 Answers4