CASE NULL value not returning case value

[sirnose1]

I have a stored procedure that when executed returns some data and writes it to a temp table. Some of the data returns and empty string. I have implemented a case function to return the word 'NONE' if the value is empty or null. However it does not seem to work. The help will be greatly appreciated. Thanks in advance.

INSERT INTO GPSNameValueTemp (GPSFieldName, GPSFieldValue)
SELECT 'CmsOut_DiagnosisAffectDrgFlag',CASE CONVERT(varchar(1000), XmlData.query('/MC_MedicareCmsOut/CmsOut_DiagnosisAffectDrgFlag/text()'))
WHEN NULL THEN 'NONE'
WHEN '' THEN 'NONE'
ELSE CONVERT(varchar(1000), XmlData.query('/MC_MedicareCmsOut/CmsOut_DiagnosisAffectDrgFlag/text()'))
END
FROM GPS.ProcessedClaimOutput WHERE ProcessedClaimID = @ProcessedClaimID AND GPSType = 3

 

[imex]

Try:

CASE CONVERT(varchar(1000), XmlData.query('/MC_MedicareCmsOut/CmsOut_DiagnosisAffectDrgFlag/text()'))
    WHEN '' THEN 'NONE'
    ELSE COALESCE(CONVERT(varchar(1000), XmlData.query('/MC_MedicareCmsOut/CmsOut_DiagnosisAffectDrgFlag/text()')), 'NONE')
END

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[sirnose1]

Thanks. Worked perfectly!