Problem with SQL Update when a field is NULL

I am having a problem with an update statement. I am using compareallvalues with a SQL data source. When one of the old values is null the update does not go through.

i.e. This does not work if something is NULL

UPDATE myTable SET something = @.something
WHERE ID = @.o_ID AND something = @.o_something

Thank You,
Jason

The reason is ANSI SQL NULL is an unknown so comparing NULL to NULL is not NULL but an unknown. Try the link below for work around. Hope this helps.

http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls_printversion.asp

|||Will there be a huge performance impact if I use Coalesce() 144 times in a statement?|||

No I think you should try SET ANSI_NULLS OFF instead. Try the link below for details from Microsoft. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_9rec.asp

|||It still does not work when I use set ANSI_NULLS OFF|||

I have two option there is a new tool that gives you intelisense in SQL Server Query Tools called SQL Prompt see if it can give you some idea of how to get the info. The other is a link with long code using COALESCE and ISNULL. Hope this helps.

http://www.red-gate.com/products/SQL_Prompt/index.htm?utm_source=sscentral&utm_medium=banner&utm_campaign=sqlprompt

http://weblogs.sqlteam.com/mladenp/articles/2937.aspx