Quick reminder: SELECT queries and NULL values in SQL Server and C#

When you are developing database application, handling NULL values can sometimes cause trouble. For instance, it is a common mistake to construct SELECT queries like this with SQL Server, if you are not paying attention:

SELECT [nn], [mm]
FROM [table]
WHERE ([somefield] = 'ABC') AND ([someotherfield] = NULL)

Can you spot the mistake? This query never returns the correct values, because the NULL value has been specified incorrectly. Of course, the remedy is to use the "IS NULL" construct instead, like this:

SELECT [nn], [mm]
FROM [table]
WHERE ([somefield] = 'ABC') AND ([someotherfield] IS NULL)

This query would then return the correct values. But, as an C# application developer, you might already be using parameterized queries, with code similar to this (error checking omitted for clarity):

SqlConnection conn = new SqlConnection(
  Properties.Settings.Default.MyConnectionString);
conn.Open();
string sql = " SELECT [nn], [mm] " +
  "FROM table " +
  "WHERE ([somefield] = @somefield) AND "+
  "([someotherfield] = @someotherfield)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@somefield", "ABC");
cmd.Parameters.AddWithValue("@someotherfield", DBNull.Value);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
  MessageBox.Show(reader.GetString(...));
}
reader.Dispose();
cmd.Dispose();
conn.Dispose();

Here, the code uses two named parameters in the SQL query: @somefield and @someotherfield. But, the code then calls the Parameters.AddWithValue method with the value of DBNull.Value.

If you don't know how the SQL Server driver executes this statement, you might be inclined to think that the WHERE parameter is executed like this:

...
WHERE ([somefield] = 'ABC') AND ([someotherfield] = NULL)

However, for some reason, the query still works as expected. How can this be? The answer is the fact that the SQL statement isn't in fact executed directly, but instead using a stored procedure called "sp_executesql". If you enable SQL Server tracing (more about this in a forth-coming blog post), you can see that a statement like this is executed:

exec sp_executesql N' SELECT [nn], [mm] FROM
table WHERE ([somefield] = @somefield) AND
([someotherfield] = @someotherfield)',N'@somefield
nvarchar(3),@someotherfield nvarchar(4000)',
@somefield=N'ABC',@someotherfield=NULL

As you can see, in this case it doesn't matter that the @someotherfield is NULL! By using the "sp_executesql" stored procedure, the SQL Server ADO.NET driver cleverly walks around the issue, and doesn't need to resort to actually changing the SQL statement if the parameter value is NULL.