How do I make an SQL statement to return all rows when a variable is blank, otherwise return only matches

Question

How do I use an SQL statement to return all rows if the input parameter is empty, otherwise only 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 is blank

How do I do it? Using CASE WHEN perhaps...

Answer 1

Just use OR?

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

Answer 2

You can use a simple OR condition:

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

Answer 3

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, unlike using OR, is index friendly: it will optimize away the redundant SELECT and use the index.

See this article in my blog for a similar problem with performance details:

• Selecting options

(this is for MySQL, but applicable to SQL Server just as well)

Answer 4

If person.location doesn't allow nulls the following will work too...

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

This assumes if all rows are required then @Location is passed as null and not an empty string ('')