How to parameterize SQL query when the parameter can sometimes be NULL?

Question

With pyodbc I can parametrize the query like this;

value = "testval"

query = \
    """
    SELECT *
    FROM TestTable
    WHERE Column = ?;
    """

cursor.execute(query, value)

But the problem is that if the value is None, the query should look like this;

value = None

query = \
    """
    SELECT *
    FROM TestTable
    WHERE Column IS NULL;
    """

cursor.execute(query)

So how should the query look like when the value can either be None or a string;

value = get_value()  # can return a string or None

query = \
    """
    SELECT *
    FROM TestTable
    WHERE Column ???????????
    """

cursor.execute(query, value)

Gordon Linoff · Accepted Answer · 2018-09-24 14:19:42Z

4

The solution is to use the ISO/ANSI standard NULL-safe comparison:

WHERE Column IS NOT DISTINCT FROM ?

Not all databases support this, so you can also use:

WHERE Column = ? OR (Column IS NULL AND ? IS NULL)

If you are reluctant to pass the parameter twice, you can include it in the FROM clause:

. . .
FROM . . . CROSS JOIN
     (SELECT ? as compColumn) params
WHERE (Column = params.compColumn0 or (Column IS NULL and params.compColumn IS NULL)

edited Sep 24, 2018 at 14:19

answered Sep 24, 2018 at 14:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ruohola Over a year ago

Am I correct that the first one will not work with sql-server?

Collectives™ on Stack Overflow

How to parameterize SQL query when the parameter can sometimes be NULL?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related