IF (@String1 <> @String2
OR (@String1 IS NULL AND @String2 IS NOT NULL)
OR (@String1 IS NOT NULL AND @String2 IS NULL)) ...
Someone saw this and decided it would be simpler to do this instead:
IF (COALESCE(@String1, '')
<>
COALESCE(@String2, '')) ...
The problem is, that doesn't work in all cases. Here is the case where it doesn't work, followed by the cases where it does work.
First, declare and set the two string variables:
DECLARE @String1 VARCHAR(20);
DECLARE @String2 VARCHAR(20);
SET @String1 = NULL;
SET @String2 = '';
Then the test:
IF (COALESCE(@String1, '')
<>
COALESCE(@String2, ''))
PRINT 'Strings Differ';
IF (@String1
<>
@String2
OR (@String1 IS NULL AND @String2 IS NOT NULL)
OR (@String1 IS NOT NULL AND @String2 IS NULL))
PRINT 'Strings Differ';
The first test does not recognize the difference, but the second test does.
All of these values pass the test:
-- 1: Both NOT NULL, different
SET @String1 = 'OldDesc';
SET @String2 = 'Desc';
-- 2: Both NOT NULL, same
SET @String1 = 'Desc';
SET @String2 = 'Desc';
-- 3: Both NULL
SET @String1 = NULL;
SET @String2 = NULL;
-- 4: One NULL, one NOT NULL
SET @String1 = 'OldDesc';
SET @String2 = NULL;