Wednesday, February 26, 2014

SQL Case with only nulls problem

Last time client reported some strange SQL error.

None of the result expressions in a CASE specification can be NULL

It is caused by following statement

UPDATE MY_TABLE SET MY_COLUMN = CASE ID  WHEN 99 THEN null WHEN 100 THEN null  END

It looks for me ok, but the problem is with nulls. When CASE expression returns only nulls, then MSSQL interpreter do not know result type. I can think abount many workarounds for this problem but the best is to add "ELSE MY_COLUMN" expression. Corrected, stable form of statement is now

UPDATE MY_TABLE SET MY_COLUMN = CASE ID  WHEN 99 THEN null WHEN 100 THEN null  ELSE MY_COLUMN END

Now interpreter knowns result type and it works.

No comments:

Post a Comment