-1
select case 'NULL' when 'NULL' then 'f' end as out

output: f

but

select case (select  (SELECT LEFT(l.list,LEN(l.list)-1) 
    FROM
    (SELECT  o.Reciting_Path + '|' + o.Declaimer +',' AS [text()]
    FROM    Poetry_Reciting  AS o
    where   o.Poetry_Id=6764
    FOR XML PATH(''))l(list)) )  when  'NULL' then 'N' end as dd

should just return NULL and NULL is correct but the case statement does not work.

I want 'N' in output

  • 3
    How could the output be 1? Shouldn't it be f? Also what is the point of performing all of that concatenation if you're never going to use the output? Wouldn't just checking for the existence of those values be sufficient? – Aaron Bertrand Jan 3 '14 at 1:40
8

There are two points of confusion here.

The first is that you seem to think 'NULL' and NULL are the same; they're not. The former is a string literal, the latter merely signifies the absence of a value.

The second is that you can use NULL in equality comparisons; and you cannot. Try this:

SELECT CASE NULL WHEN NULL THEN 1 ELSE 0 END;

Result:

----
0

The reason is that NULL is unknown - so SQL Server can't tell you whether it is or is not equal to a specific value (or even to another NULL value). To check if a value is NULL, you can't say CASE WHEN value = NULL or CASE value WHEN NULL, but you can say CASE WHEN value IS NULL:

SELECT CASE WHEN NULL IS NULL THEN 1 ELSE 0 END;

Result:

----
1

Now, I think there are better ways to accomplish what you seem to need (I'm not confident I understand the requirements here at all), but in the immediate term you can correct this by saying:

select case WHEN (select ... )  IS NULL then 'N' end as dd;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.