C# Parameterized Snowflake Query where value can be null not working as expected-CodePudding

When using parameterized queries in C# in the WHERE statement how do you handle values that can be null?

Let's say we have a table:

ID|NAME|TITLE
1|'Bob'|"Boss'
2|'Bobbet'|NULL

Let's say that I have a simple query of

var commandText = "SELECT ID, NAME, TITLE FROM PEOPLE WHERE NAME=? AND TITLE=?";

I have two DBParameters that map to an object's properties like thus:

DbParameter nameParam = new SqlParameter("1", DbType.String);
nameParam.Value = string.IsNullOrWhiteSpace(input.Name)? DBNull.Value : input.Name;
DbParameter titleParam = new SqlParameter("2", DbType.String);
titleParam.Value = string.IsNullOrWhiteSpace(input.Title)? DBNull.Value : input.Title;

When the query is executed for Bob like thus: The expected row returns.

SELECT ID, NAME, TITLE FROM PEOPLE WHERE NAME='Bob' AND TITLE='Boss';

When the query is executed for Bobbet like thus: The expected row doesn't return.

SELECT ID, NAME, TITLE FROM PEOPLE WHERE NAME='Bobbet' AND TITLE=NULL;

However if I alter the query to this: The expected row does return.

SELECT ID, NAME, TITLE FROM PEOPLE WHERE NAME='Bobbet' AND TITLE IS NULL;

How in code are you suppose to pass possible null concepts using a parameterized query? Or, do I have to use a conditional string builder to handle the possible null values when building the command text?

CodePudding user response：

One way of handling it is to use IS NOT DISTINCT FROM(predicate is part of SQL Standard) or EQUAL_NULL function. Both are NULL-safe:

var commandText = "SELECT ID, NAME, TITLE 
                   FROM PEOPLE 
                   WHERE NAME IS NOT DISTINCT FROM ? 
                     AND TITLE IS NOT DISTINCT FROM ?";


var commandText = "SELECT ID, NAME, TITLE 
                   FROM PEOPLE 
                   WHERE EQUAL_NULL(NAME, ?) 
                     AND EQUAL_NULL(TITLE, ?)";

db<>fiddle demo

CodePudding user response：

The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE. Always use the IS NULL clause to validate if a variable is NULL.

Source: Understanding the difference between IS NULL and = NULL