I want to make sure I'm not inserting a duplicate row into my table (e.g. only primary key different). All my field allow NULLS as I've decided null to mean "all values". Because of nulls, the following statement in my stored procedure can't work:
IF EXISTS(SELECT * FROM MY_TABLE WHERE
MY_FIELD1 = @IN_MY_FIELD1 AND
MY_FIELD2 = @IN_MY_FIELD2 AND
MY_FIELD3 = @IN_MY_FIELD3 AND
MY_FIELD4 = @IN_MY_FIELD4 AND
MY_FIELD5 = @IN_MY_FIELD5 AND
MY_FIELD6 = @IN_MY_FIELD6)
BEGIN
goto on_duplicate
END
since NULL = NULL is not true.
How can I check for the duplicates without having an IF IS NULL statement for every column?