How to force SQL statement to return all rows when variable is empty, otherwise only matches are returned
How do I use an SQL statement to return all rows if the input parameter is empty, otherwise return rows that match?
My SQL looks like this:
where person.personstatusuid = @StatusUID
AND person.forename != ''
AND person.location = @Location
but I want basically person.location = @Location OR @Location empty
How should I do it? Using CASE WHEN it is possible ...
SELECT *
FROM mytable
WHERE person.personstatusuid = @StatusUID
AND person.forename != ''
AND person.location = @Location
UNION ALL
SELECT *
FROM mytable
WHERE person.personstatusuid = @StatusUID
AND person.forename != ''
AND @Location IS NULL
This solution, in contrast to use OR
, is index-friendly: it optimizes the redundancy SELECT
and uses the index.
See this article on my blog for a similar performance spec issue:
(this is for MySQL
but also applies to SQL Server
)
source to share
If person.location does not allow NULL, the following will work too ...
where person.personstatusuid = @StatusUID
AND person.forename != ''
AND person.location = isnull(@Location,person.location)
This assumes all strings are required and @Location is passed as null and not an empty string ('')
source to share