CODEWITHSUNDEEP
sqlparameterswhere-clause
Fseee
Fseee

Reputation: 2631

sql query if parameter is null select all

Can the following query be modified to return all records if the ? is null?

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = ?;

Upvotes: 63

Views: 129229

Answers (4)

variable
variable

Reputation: 9684

The foll. query will handle the case where the Name (table column value) can also be NULL:

SELECT NAME, SURNAME FROM MY_TABLE WHERE COALESCE(NAME,'') = COALESCE(?,NAME,'');

Upvotes: 2

Pushkar Saxena
Pushkar Saxena

Reputation: 1

SELECT NAME
FROM MY_TABLE
WHERE NAME LIKE CASE WHEN ? IS NOT NULL THEN ? ELSE '%' END

This will work perfectly but it will return only the not null values.

Upvotes: 0

Robert
Robert

Reputation: 25753

You can also use functions IFNULL,COALESCE,NVL,ISNULL to check null value. It depends on your RDBMS.

MySQL:

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = IFNULL(?,NAME);

or

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = COALESCE(?,NAME);

ORACLE:

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = NVL(?,NAME);

SQL Server / SYBASE:

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = ISNULL(?,NAME);

Upvotes: 46

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79939

Try this:

SELECT * 
FROM MY_TABLE 
WHERE @parameter IS NULL OR NAME = @parameter;

Upvotes: 93

Related Questions