CODEWITHSUNDEEP
sqlsql-server
st1612
st1612

Reputation: 33

NULL check in WHEN statement is not working in SQL Server

I expect 'No Conversion' should be printed for the below code but instead receiving the following error 'Error converting data type varchar to numeric.' When the TRY_PARSE inside the WHEN statement returns null then the THEN block should execute?

DECLARE @VALUE varchar(20) = 'NA'

SELECT
    CASE
       WHEN TRY_PARSE(@VALUE AS DECIMAL) IS NULL
          THEN 'No Conversion'
          ELSE (CONVERT(DECIMAL(10, 3), @VALUE, 0))
    END

Upvotes: 0

Views: 47

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522084

All branches of a CASE expression need to have the same type. Given that the error message is text, you should also be including the cast decimal value as text, something like this:

SELECT CASE WHEN TRY_PARSE(@VALUE AS DECIMAL) IS NULL
            THEN 'No Conversion'
            ELSE CONVERT(varchar(max), CONVERT(decimal(10,3), @VALUE, 0)) END

Upvotes: 5

Related Questions