CODEWITHSUNDEEP
sqlsql-server
Andrew Rutz
Andrew Rutz

Reputation: 23

CASE WHEN converting smallint to varchar

I have searched a bit on either using cast or convert but failed to succeed in my query.

This is my query right now:

CASE WHEN ENR.GR = '-1' THEN 'TK'
WHEN ENR.GR = '0' THEN 'K'
ELSE ENR.GR
END AS 'Grade'

I have tried using case and convert like this:

CASE WHEN ENR.GR = '-1' THEN CAST('TK' as varchar(2))
WHEN ENR.GR = '0' THEN CAST('K' as varchar(1))
ELSE ENR.GR
END AS 'Grade'

I haven't been able to run it without getting the 

"Conversion failed when converting the varchar value 'TK' to data type 
smallint." error.

Sorry this has probably been asked multiple times, if someone could explain what I am doing wrong or point me to the right direction, I would appreciate it.

Upvotes: 2

Views: 1931

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272386

Assuming that gr column is smallint, you need to cast it to varchar:

CASE
    WHEN ENR.GR = -1 THEN 'TK'
    WHEN ENR.GR =  0 THEN 'K'
    ELSE CAST(ENR.GR AS VARCHAR(6))
END AS 'Grade'

This is because the CASE expression returns exactly one datatype and it attempts to convert result from all WHEN and ELSE branches to that datatype. The resultant datatype is chosen based on precedence. Since smallint has higher priority than varchar, it attempts to convert 'DK' and 'K' to smallint.

Upvotes: 1

Icculus018
Icculus018

Reputation: 1076

You need to cast the smallint to varchar.

DECLARE @gr smallint = -1;

SELECT 
    CASE WHEN @gr = '-1' THEN 'TK' ELSE CASE WHEN @gr = '0' THEN 'K' ELSE CAST(@gr AS VARCHAR(10)) END END AS Grade

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Your conversation should go with ELSE part : 

CASE WHEN ENR.GR = '-1' 
     THEN 'TK'
     WHEN ENR.GR = '0' 
     THEN 'K'
     ELSE CAST(ENR.GR AS VARCHAR(255))
END AS 'Grade'

You don't need to use single quote with numeric value. Just use :

CASE WHEN ENR.GR = -1
     THEN 'TK'
     WHEN ENR.GR = 0
     THEN 'K'
     ELSE CAST(ENR.GR AS VARCHAR(255))
END AS 'Grade'

Upvotes: 2

Related Questions