January 18, 2018 at 10:53 am
I have a situation where I'm trying to determine if a value has changed. I expected to be able to determine easier if the record had NOT changed by doing the following:WHERE NOT ((@OldValue IS NULL AND @NewValue IS NULL) OR (@OldValue = @NewValue))
however the above situation fails to work whenever either @OldValue or @NewValue are NULL. Interestingly, if I do the following it works just fine:WHERE CASE WHEN ((@OldValue IS NULL AND @NewValue IS NULL) OR (@OldValue = @NewValue)) THEN 0 ELSE 1 END = 1
Apparently the NOT operator is failing. While I do have a work-around, I'm curious if anyone has an idea why the NOT operator fails. I know that there are some who will say for me to not use NULLs, but as with many of us, I don't have a choice in this matter as this is what I have to work with at the moment.
Here is a bit of sample code that replicates the issue:DECLARE @OldValue INT,
@NewValue INT;
SELECT @OldValue AS "Old Value", @NewValue AS "New Value", 'Value is changed' AS msg
WHERE NOT ((@OldValue IS NULL AND @NewValue IS NULL) OR (@OldValue = @NewValue));
SET @NewValue = 1;
SELECT @OldValue AS "Old Value", @NewValue AS "New Value", 'Value is changed' AS msg
WHERE NOT ((@OldValue IS NULL AND @NewValue IS NULL) OR (@OldValue = @NewValue));
SET @OldValue = @NewValue;
SET @NewValue = 0;
SELECT @OldValue AS "Old Value", @NewValue AS "New Value", 'Value is changed' AS msg
WHERE NOT ((@OldValue IS NULL AND @NewValue IS NULL) OR (@OldValue = @NewValue));
SET @OldValue = @NewValue;
SET @NewValue = null;
SELECT @OldValue AS "Old Value", @NewValue AS "New Value", 'Value is changed' AS msg
WHERE NOT ((@OldValue IS NULL AND @NewValue IS NULL) OR (@OldValue = @NewValue));
SET @OldValue = null;
SET @NewValue = null;
SELECT @OldValue AS "Old Value", @NewValue AS "New Value", 'Value is changed' AS msg
WHERE CASE WHEN ((@OldValue IS NULL AND @NewValue IS NULL) OR (@OldValue = @NewValue)) THEN 0 ELSE 1 END = 1;
SET @NewValue = 1;
SELECT @OldValue AS "Old Value", @NewValue AS "New Value", 'Value is changed' AS msg
WHERE CASE WHEN ((@OldValue IS NULL AND @NewValue IS NULL) OR (@OldValue = @NewValue)) THEN 0 ELSE 1 END = 1;
SET @OldValue = @NewValue;
SET @NewValue = 0;
SELECT @OldValue AS "Old Value", @NewValue AS "New Value", 'Value is changed' AS msg
WHERE CASE WHEN ((@OldValue IS NULL AND @NewValue IS NULL) OR (@OldValue = @NewValue)) THEN 0 ELSE 1 END = 1;
SET @OldValue = @NewValue;
SET @NewValue = null;
SELECT @OldValue AS "Old Value", @NewValue AS "New Value", 'Value is changed' AS msg
WHERE CASE WHEN ((@OldValue IS NULL AND @NewValue IS NULL) OR (@OldValue = @NewValue)) THEN 0 ELSE 1 END = 1;
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
January 18, 2018 at 11:16 am
Instead of this
WHERE NOT ((@OldValue IS NULL AND @NewValue IS NULL) OR (@OldValue = @NewValue))
Try this
WHERE NOT EXISTS (SELECT @OldValue INTERSECT SELECT @NewValue)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 18, 2018 at 12:00 pm
Phil Parkin - Thursday, January 18, 2018 11:16 AMInstead of this
WHERE NOT ((@OldValue IS NULL AND @NewValue IS NULL) OR (@OldValue = @NewValue))
Try this
WHERE NOT EXISTS (SELECT @OldValue INTERSECT SELECT @NewValue)
That, my friend, is a smashing good idea and it works quite well! I never thought of using INTERSECT with single values before.
Thanks a bunch!
LinkedIn: http://www.linkedin.com/in/sqlrv
Twitter: http://www.twitter.com/sqlrv
Website: http://sqlrv.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply