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

+2


source to share


4 answers


Just use OR?



where person.personstatusuid = @StatusUID
AND person.forename != ''
AND (person.location = @Location or IsNull(@location,'')='')

      

+2


source


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

)

+2


source


You can use a simple OR condition:

where person.personstatusuid = @StatusUID
           AND person.forename != ''
           AND (person.location = @Location OR @Location IS NULL)

      

+1


source


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

+1


source







All Articles