Compare varchar(max) with NULL value - Fran

I have a trigger which looks at fields which are updated and inserts the
changes into a AuditLog table.

For example:
IF UPDATE([PromisedDeliveryDateTo])
BEGIN
INSERT INTO [dbo].[AuditLog]

([TableName],[TableNameID],[FieldName],[Type],[OldValue],[NewValue],[UserID],[UpdateTime])
SELECT 'Shipments'
,i.ShipmentID
,'PromisedDeliveryDateTo', 'Update'
, d.PromisedDeliveryDateTo
, i.PromisedDeliveryDateTo
, i.LastUpdatedUser, getdate()
FROM Inserted i INNER JOIN Deleted d ON i.ShipmentID =d.ShipmentID
WHERE COALESCE(i.PromisedDeliveryDateTo,-1) <>
COALESCE(d.PromisedDeliveryDateTo,-1)
END

The coalesce does the trick for determining null values. this works fine.

But with a varchar(max) field this doesn't work. When I update a record it
says: Conversion failed when converting the varchar value 'xxxx' to int"
If I don't use the coalesce the trigger will not be executed when a null
value is involved (the new or old value is NULL).

What can I do filter only when there are changes in the varchar(max) field?

Thanks,
Frans

Compare varchar(max) with NULL value - Aaron Bertrand [SQL Server MVP]

Well, why are you trying to convert a VARCHAR(MAX) column to -1?  How about:

WHERE COALESCE(i.column, '') <> COALESCE(d.column, '')

?  Except that will break in the case where a column value was updated from
NULL to '' or vice versa.  So, maybe you could give CHECKSUM() a try?

WHERE CHECKSUM(i.column) <> CHECKSUM(d.column)

A

Compare varchar(max) with NULL value - Plamen Ratchev

The COALESCE function returns the first non-null expression from its
arguments with return data type of the highest data type precedence. Since
you have VARCHAR(MAX) column and INT expression of -1, it converts the
VARCHAR(MAX) values to INT because it is of higher data type precedence.

To avoid this problem you can replace COALESCE(column, -1) with
COALESCE(column, '-1') which will have compatible return data type of all
arguments.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Compare varchar(max) with NULL value - Hugo Kornelis

(snip)

Hi Frans,

One problem with your code for numeric columns is that you use -1 as a
magic value - what if some column has -1 as a normal value in it's
domain?

The solutions offered by Aaron and Plamen suffer the same problem. They
use '' (empty string) or '-1' as magic values. If you are sure these
values will never be valid data, then that should not be a problem.
However, a generic approac that does not rely on magic values will have
to use explicit tests for NULL as follows:

WHERE i.PromisedDeliveryDateTo <> d.PromisedDeliveryDateTo
OR (  i.PromisedDeliveryDateTo IS NOT NULL
AND d.PromisedDeliveryDateTo IS NULL)
OR (  i.PromisedDeliveryDateTo IS NULL
AND d.PromisedDeliveryDateTo IS NOT NULL)

Or, a slightly shorter version:

WHERE NOT(   i.PromisedDeliveryDateTo = d.PromisedDeliveryDateTo
OR(    i.PromisedDeliveryDateTo IS NULL
AND d.PromisedDeliveryDateTo IS NULL))

And if you really want to have it short and snappy (but harder to
understand)

WHERE NULLIF(i.PromisedDeliveryDateTo, d.PromisedDeliveryDateTo) IS NOT
NULL
AND   NULLIF(d.PromisedDeliveryDateTo, i.PromisedDeliveryDateTo) IS NOT
NULL

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Compare varchar(max) with NULL value - Aaron Bertrand [SQL Server MVP]

Hey, I "disclaimered" that, to some extent.  :-)

Compare varchar(max) with NULL value - Hugo Kornelis

On Tue, 06 May 2008 17:45:46 -0400, Aaron Bertrand [SQL Server MVP]


Hi Aaron,

Yes you did. Sorry for missing that.

However, the CHECKSUM method you then mentioned won't help. Different
varchar values CAN result in the same checksum value! And I don't know
enough about the algorithm checksum uses to verify whether there is any
varchar(max) value that results in the same checksum as a varchar(max)
value of NULL (i.e. 2147483647).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Compare varchar(max) with NULL value - Aaron Bertrand [SQL Server MVP]

You're right, I did very limited testing where it worked flawlessly, but
after re-reading the docs on checksum(), it is clear that this wouldn't be
practical for large string values.  It's too bad because it would be a
really nice shortcut.