CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008
Cegone
Cegone

Reputation: 489

Where clause in SQL Query skip date filter when date is null

I have search query with parameters are Name, From date, To date. When i pass empty string or null to date parameters it should return all data matches with name only. This code is not working for me.

WHERE name=@name 
AND 
(CASE WHEN @_from IS not null 
            THEN
                Servicedate<=@_from 
            END)

This is also not woking for me.

WHERE name = @name AND
Servicedate>=ISNULL((case when @_from='' then NULL else @_from),Servicedate)

Thank You.

Upvotes: 1

Views: 5939

Answers (3)

Masood
Masood

Reputation: 189

WHERE
(name=@name AND @_from IS NOT NULL AND @_from <> '' AND Servicedate<=@_from )
OR
(name=@name AND (@_from IS null or @_from = ''))

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use this:

WHERE
    name = @name
    AND (@_from IS NULL OR ServiceDate >= @_from)
    AND (@_to IS NULL OR ServiceDate <= @_to)

If the variables(@_from and @_to) are unset, the condition will return true since @var IS NULL is true.

Upvotes: 5

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

WHERE ( name=@name AND @_from IS NOT NULL AND Servicedate<=@_from )
      OR
      ( name=@name AND @_from IS null )

If date parameter @_from is empty then searching is done only on name parameter @name else ServiceDate column is matched against date parameter @_from

Upvotes: 1

Related Questions