3

I'm using C# to write to a SQL Compact Edition 3.5 database. I got a table containing e-mail addresses and names for each address.

MailRecipientAddressID int primary key identity(1,1) not null,
Address nvarchar(4000),
Name nvarchar(4000)

In this table I want every address-name combination to be unique. In some cases it's possible that either Address or Name is NULL in a row. Before inserting new rows into this table, I'm using a SELECT query to check if there is an existing row matching the one I want to insert. When using this query

SELECT MailRecipientAddressID FROM MailRecipientAddress WHERE Address = @Address AND Name = @Name

I will not find existing rows with NULL values in one column (see here).

Now I got this query, which works and kind of solves my problem

SELECT MailRecipientAddressID FROM MailRecipientAddress WHERE ISNULL(Address, '') = ISNULL(@Address, '') AND ISNULL(Name, '') = ISNULL(@Name, '')

but even though it is no problem in my case that NULL and empty string values are handled equally, I do not like this solution. I think it's kind of hackish. Is there a better approach

  • to apply a filter on a SELECT statement with parameters which can contain NULL
  • which works on SQL CE


Edit

I do not understand why, but my query works with SQL Management Studio but it does not in my application (see here). To correct my own approach I would need to use COALESCE.

I don't like the option to replace my NULL values with empty strings because I think it would be kind of inconsequent to set a value at a place where I got no value or is my understanding of this design question wrong?

Community
  • 1
  • 1
Georg Jung
  • 949
  • 10
  • 27
  • Can you avoid `NULL` in both address and name? If you force them to have some value, e.g. `''`, you will not have such problem, as well as need to specially process `NULL` value. – Serg Aug 25 '14 at 11:26

3 Answers3

2

The best solution is a constraint on the table that prevents duplicates from going into the table. You can put one in with a unique index:

create unique index idx_MailRecipientAddress_address_name on MailRecipientAddress(Address, Name);

This will generate an error on the insert, which you would then need to catch.

However, this is only a partial solution, because NULL values do not count as duplicates. You might solve your overall problem by not allowing NULL values in the field at all. Instead, represent no data using empty strings. Note: I wouldn't normally recommend this. In SQL, NULL means "unknown" and by the definition of the language, two "unknown" values are not equal. However, you seem to want them to be equal.

As for SQL, yours is okay, but it equates NULL and the empty string. An explicit check is more accurate:

WHERE (Address = @Address or Address is null and @Address is null) and
      (Name = @Name or Name is null and @Name is null)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • So if you tell I should not try to make `NULL`s equal by design because they are not, how should I change my table structure so that I don't get duplicate entries (see my edit)? – Georg Jung Aug 25 '14 at 11:42
  • In a mailing address table, it probably makes sense that both columns are declared to be `NOT NULL`. If they are in the table, the values should be there. – Gordon Linoff Aug 25 '14 at 11:46
  • 1
    I tend to use your explicit check, but I get the following error: `Der für die Funktion angegebene Argumentwert ist ungültig. [ Argument # = 1,Name of function(if known) = isnull ]`. In english like `The argument passed for the function is not valid.` When executing the query in Management Studio using hard coded string values everything works fine but not using the parameterized query. – Georg Jung Aug 25 '14 at 12:08
0

@George

if Parameter value is Null and column value is not null then "(Address = @Address or Address is NULL) returns false "

if Parameter value is Null and column value is null then "(Address = @Address or Address is NULL) returns true"

if Parameter value is Not Null and column value is null then "(Address = @Address or Address is NULL) returns true"

if Parameter value is Not Null and column value is Not null and if matches then "(Address = @Address or Address is NULL) returns true otherwise false"

Navneet
  • 447
  • 4
  • 13
  • Yes that is right, but I need `if Parameter value is Not Null and column value is null then "(Address = @Address or Address is NULL) returns true"` to return false, not true. Gordons answer handles that case. – Georg Jung Aug 25 '14 at 12:34
-1
SELECT MailRecipientAddressID FROM MailRecipientAddress WHERE (Address = @Address or Address is NULL) AND (Name = @Name or Name is NULL)
Navneet
  • 447
  • 4
  • 13
  • `Address = @Address or Address is NULL` will evaluate to `TRUE` even if my value in `@Address` is not null. Changing this accordingly would result in the same query @Gordon provided in his answer, as far as I can see. – Georg Jung Aug 25 '14 at 12:05
  • 1
    As far as I can see, Gordons query handles the cases that 1) the column value and the parameter are `NULL` 2) both are not `NULL` but equal 3) both are not `NULL` and not equal 4) the column value is `NULL` while the parameter isn't 5) the parameter value is `NULL` while the column value isn't. Your approch misses case 5). – Georg Jung Aug 25 '14 at 12:17