SQL Server parameterized query with a WHERE clause with Nulls

Question

VB.NET 2012, ADO.NET, SQL Server 2014

I setup a parameterized query that works well. I essentially read records from a DataTable that comes from a different source than my SQL Server. It's small enough that I elected to read record by record and build a query and hit the SQL Server for a match.

However I am having trouble getting a match when one of my fields is supposed to be matched for a null. I know a record exist because I can look at it in SQL Server directly and see it. With my parameterized query somehow the null is being translated improperly. I tried manually replacing the parameter @EngSerialNo with DBNull.Value and still doesn't work. Almost seems like I need two different queries depending if my DataTable value is null.

sqQry.AppendLine("SELECT CityCode,CarNum,RegNum,Event,EngSerialNum)")
sqQry.AppendLine("FROM [MyDB].[dbo].[Events]")
sqQry.AppendLine("WHERE (CityCode=@City AND CarNum=@CarNo AND RegNum=@RegNo AND Event=@Event AND EngSerialNum=@EngSerialNo)") 'this looks for a value in EngSerialNo
'sqQry.AppendLine("WHERE (CityCode=@City AND CarNum=@CarNo AND RegNum=@RegNo AND Event=@Event AND EngSerialNum IS NULL)")     'this looks for a Null in EngSerialNo     

 Dim cmd As New SqlCommand

 With cmd
        .Connection = connMyDb
        .CommandType = CommandType.Text
        .CommandText = sqQry.ToString

         'cycle through each DataRow in the DataTable and check for returns
         Dim total As Integer = 0
         For Each row As DataRow In dtMain.Rows
             .Parameters.Clear()
             .Parameters.AddWithValue("@City", row.Item("City"))
             .Parameters.AddWithValue("@CarNo", row.Item("CarNo"))
             .Parameters.AddWithValue("@RegNo", row.Item("RegNo"))
             .Parameters.AddWithValue("@Event", row.Item("Event"))
             .Parameters.AddWithValue("@EngSerialNo", row.Item("EngSerialNo")) 'how do I get this to look for a null value when the DataTable contains a null value?

             Dim rowsAffected As Integer = .ExecuteNonQuery()
             total += rowsAffected
         Next row
End With

Update: I ended up creating a dynamic SQL for every DataRow. Basically for each DataRow I check key fields for NULL or an actual value and create the appropriate SQL command text. I have 4 fields that could contain a NULL but for sake of simplicity I only demonstrated one here. I think the developer can follow the example to create their own query.

 Dim cmd As New SqlCommand
 With cmd
        .Connection = connMyDb
        .CommandType = CommandType.Text

         'cycle through each DataRow in the DataTable and check for returns
         Dim total As Integer = 0
         For Each row As DataRow In dtMain.Rows
             .CommandText = BuildSql(row)

             .Parameters.Clear()
             .Parameters.AddWithValue("@City", row.Item("City"))
             .Parameters.AddWithValue("@CarNo", row.Item("CarNo"))
             .Parameters.AddWithValue("@RegNo", row.Item("RegNo"))
             .Parameters.AddWithValue("@Event", row.Item("Event"))
             .Parameters.AddWithValue("@EngSerialNo", row.Item("EngSerialNo")) 

             Dim rowsAffected As Integer = .ExecuteNonQuery()
             total += rowsAffected
         Next row
End With

Private Function BuildSql(ByVal dr As DataRow) As String
     Dim sqQry As New StringBuilder
     sqQry.AppendLine("SELECT CityCode,CarNum,RegNum,Event,EngSerialNum)")
     sqQry.AppendLine("FROM [MyDB].[dbo].[Events]")

     If dr.Item("EngSerialNo") Is DBNull.Value Then
        sqQry.AppendLine("WHERE (CityCode=@City AND CarNum=@CarNo AND RegNum=@RegNo AND Event=@Event AND EngSerialNum IS NULL)")     'this looks for a Null in EngSerialNo            
     Else
        sqQry.AppendLine("WHERE (CityCode=@City AND CarNum=@CarNo AND RegNum=@RegNo AND Event=@Event AND EngSerialNum=@EngSerialNo)") 'this looks for a value in EngSerialNo
     End If

     Return sqQry.ToString
End Function

score 3 · Accepted Answer · answered Nov 14 '14 at 22:59

3

In SQL you can't compare null values, i.e. EngSerialNum = null always evaluates to false, even if the value in the field is null.

Either you can create the query dynamically so that you use is null to match the null values, or you can use an expression like this:

((EngSerialNum is null and @EngSerialNo is null) or EngSerialNum = @EngSerialNo)

answered Nov 14 '14 at 22:59

Guffa

687,336
108
737
1,005

Well I did some research and it was stated that setting ANSI_NULLS on the database itself as an option would do what I need. http://msdn.microsoft.com/en-us/library/ms188048.aspx Tried it and it didn't work. I'll give your suggestion a go. I actually have two fields that I need to check for so luckily I don't have to write a huge alternative query. – sinDizzy Nov 15 '14 at 23:34
@sinDizzy: You would use `set ansi_nulls off` to be able to compare null values. However, that possibility will be removed in the future. Ref: http://msdn.microsoft.com/en-us/library/ms188048.aspx – Guffa Nov 16 '14 at 01:49
Ah yes I see what you mean now. So will implement my query that is not dependent on that setting. In other words use an IS NULL. I'll post once I have something that works. – sinDizzy Nov 16 '14 at 02:39
I ended up creating a dynamic SQL based on the value of each field that can be NULL. Its a bit more work but I don't have that many records to cycle through. Updated my post with pseudo code of the solution. – sinDizzy Nov 18 '14 at 19:58
Nice work with the expression. That also has the added benefit that it doesn't bloats the query cache with many similar plans. – Alejandro Aug 25 '16 at 14:22

SQL Server parameterized query with a WHERE clause with Nulls

1 Answers1

Linked