CODEWITHSUNDEEP
sqlsql-servert-sql
Gisli
Gisli

Reputation: 734

Check whether image column is null

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?

Upvotes: 3

Views: 13731

Answers (3)

Martin Smith
Martin Smith

Reputation: 453563

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.

Upvotes: 1

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385264

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

Upvotes: 5

Craig Mellon
Craig Mellon

Reputation: 5399

Try the following:

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

Upvotes: 2

Related Questions