Using SqlCommand to Select data and then Insert it if it does not exist, data can be NULL on occasion

Question

I am trying to Insert data into a SQL table, only if the data does not currently exist. The issue comes in, from what i have found when a Null is passed in.

I can get the C# SqlCommand to work when all values are present, the moment i add a Null i get an issue, the issue being it upload's the same line again.

using (SqlCommand cmd = new SqlCommand("BEGIN IF NOT EXISTS (SELECT * FROM dbo.tb_someTable WHERE someProperty = '@someProperty') Begin Insert into tb_someTable (someProperty) Values (@someProperty) END END",conn))
{
CheckNullorEmpty(cmd,"@someProperty",someProperty);
cmd.ExecuteNonQuery();
}

The expected output is I only want a new record Inserted if a previous one does not exist.

I think this answer explains why https://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server. You're expecting `someNullParam = null` to evaluate to `true`, but it does not. — steve16351, Aug 14 '19 at 08:21
Thank you @steve16351, I was not aware of this behavior. I currently do not have a PK on the table and in order to ensure I always check against unique data I will create a composite key and check against it. — Tristan, Aug 14 '19 at 08:30
If you re-write your condition as `WHERE someProperty = @someProperty OR (someProperty IS NULL AND @someProperty IS NULL)`, that should work — steve16351, Aug 14 '19 at 08:37
@steve16351, This did the trick, how can i mark the comment as answering the question? or if possible do you mind posting it as an answer. One last thing, Im not sure if its allowed but perhaps also add as to why it would work now when adding the OR (someProperty IS NULL ...). I know you posted a very valuable link to the information above on your first comment. — Tristan, Aug 14 '19 at 08:51

score 0 · Answer 1 · answered Aug 14 '19 at 08:25

0

I'm not quiet familiar with the C# syntax, but this SQL should work:

Insert into tb_someTable (someProperty) Values (@someProperty) WHERE NOT EXISTS (SELECT * FROM dbo.tb_someTable WHERE someProperty = '@someProperty')

answered Aug 14 '19 at 08:25

dewey

809
2
16
47

I tried the above, but it is complaining around the where clause used. Looking at the above stackoverflow link @steve16351 provided I can tell that the NULL value still would not evaluate the way i would want it to. – Tristan Aug 14 '19 at 08:52

score 0 · Accepted Answer · answered Aug 14 '19 at 09:00

The problem is that in SQL, you cannot compare NULL with a non-null value using the equality operator. This answer explains in more detail why that is the case. In other words, if we have a condition such as this:

@someParameter = NULL

When @someParameter contains NULL, this expression will evaluate to FALSE - so your IF NOT EXISTS.. clause overall is true and it goes ahead and inserts another NULL value even if there is already one in the table.

You can change your WHERE condition to handle the NULL comparison as follows:

WHERE someProperty = @someProperty OR (someProperty IS NULL AND @someProperty IS NULL)

This is now saying "if someProperty is the same value as @someProperty or someProperty has no value and @someProperty has no value". So the first part as you had before handles the value comparison, and the second part handles the null check.

Using SqlCommand to Select data and then Insert it if it does not exist, data can be NULL on occasion

2 Answers2