sql query if parameter is null select all

SqlParametersWhere Clause

Sql Problem Overview

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

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = ?;

Sql Solutions

Solution 1 - Sql

Try this:

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

Solution 2 - Sql

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

Solution 3 - Sql

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 4 - Sql

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.

Categories
Recommended Sql Solutions
Recommended Parameters Solutions
Recommended Where Clause Solutions

Previous Solution:

Regex number between 1 and 100

RegexString

Next Solution:

In Visual Basic how do you create a block comment

vb.net

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionFseeeView Question on Stackoverflow
Solution 1 - SqlMahmoud GamalView Answer on Stackoverflow
Solution 2 - SqlRobertView Answer on Stackoverflow
Solution 3 - SqlvariableView Answer on Stackoverflow
Solution 4 - SqlPushkar SaxenaView Answer on Stackoverflow