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

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

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)