How do I show all records when one field is null in SQL?

Question

I have my code

SELECT ID, NAME, ADDRESS FROM CUSTOMER WHERE NAME = @NAME

but the @NAME is an optional field for searching.

If the @NAME is blank or NULL, it should show all records.

How do I approach this?

Thanks!

Answer 1

It will not select all records if @NAME is null or blank because if the WHERE condition is not true, nothing will be selected. (The only way something is selected with your current query is if this WHERE condition is true. You will have to use a different query.

Answer 2

There are some ways to do it the easy way. I would do something like this. You can use the Function ISNULL to handle blank or NULL.

SELECT ID, NAME, ADDRESS 
FROM CUSTOMER 
WHERE ISNULL(@NAME,'') = '' OR NAME = @NAME

With this statement you will get all records if @NAME is blank or NULL. If @NAME is not blank and not NULL, it will return all records that match @NAME.

If you have more optional fields you can use or. But dont forget the parenthesis.

SELECT ID, NAME, ADDRESS 
FROM CUSTOMER 
WHERE 
    (ISNULL(@NAME,'') = '' OR NAME = @NAME)
    OR
    (ISNULL(@ADDRESS,'') = '' OR ADDRESS = @ADDRESS)

Answer 3

You are best off with a straight IF statement:

IF (@NAME IS NULL)
    SELECT ID, NAME, ADDRESS
    FROM CUSTOMER
    WHERE NAME = @NAME;
ELSE
    SELECT ID, NAME, ADDRESS
    FROM CUSTOMER;