'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 = ?;
sqlparameterswhere-clause


Solution 1:[1]

Try this:

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

Solution 2:[2]

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,'');

Solution 3:[3]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mahmoud Gamal
Solution 2 variable
Solution 3 Pushkar Saxena

Related Questions