Check whether image column is null

Question

I need to find out whether an image column in table is null. I've been trying to use CASE but I always get an error.

Query:

SELECT  OutgoindDoc = CASE ReceivedData       
                        WHEN null THEN 'null'
                        ELSE CONVERT(xml,(CONVERT(varbinary(max),ReceivedData))) 
                      END
FROM    ib_IncomingData 

And the error I'm getting:

Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

What can I use to get the results I need?

Solution 1

CASE <expression> WHEN <value> THEN uses equality/equivalence comparison, but you need an IS NULL check because NULL is not a comparable quantity and — as the error indicates — images can't be "compared".

Fortunately, there is another construct — CASE WHEN <test> THEN — that brings the equality out into the user-provided parameters, allowing you to omit it:

SELECT  OutgoindDoc = CASE
                        WHEN ReceivedData IS NULL THEN
                           'null'
                        ELSE
                           CONVERT(xml,(CONVERT(varbinary(max),ReceivedData))) 
                      END
FROM    ib_IncomingData

Solution 2

Try the following:

SELECT  OutgoindDoc = CASE       
                        WHEN ReceivedData IS NULL THEN 'null'
                        ELSE CONVERT(xml,(CONVERT(varbinary(max),ReceivedData))) 
                      END
FROM    ib_IncomingData 

Solution 3

A more concise version

SELECT  OutgoindDoc = 
         ISNULL(CONVERT(xml,(CONVERT(varbinary(max),ReceivedData))) ,'null')
FROM    ib_IncomingData 

Although from the comments it seems you don't really need to do this anyway.