SQL Server Convert in Case Statement

Question

I have a column called compositeRate, it is a decimal(10,2) datatype. I am writing a Select statement that should return empty string if compositeRate is 0.00 however I am getting this error: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.

This is my case statement

select CASE WHEN compositeRate = 0.00 THEN '' else compositeRate from table

Could you please let me know where should I write convert in my case statement?

score 6 · Accepted Answer · edited Jul 05 '14 at 23:00

You will need to convert compositeRate to a string in the else part:

SELECT CASE 
          WHEN compositeRate = 0.00 THEN CAST('' AS Varchar(20))
          ELSE CAST(compositeRate AS VARCHAR(20))
       END          AS compositeRate 
  FROM table

or use CONVERT with the appropriate values.

Right now you have a CASE expression that returns either a string or a number, and you have to decide which is the right type to return. One CASE expression must return one type.

score 0 · Answer 2 · edited Dec 04 '19 at 20:36

I looked at this thread to solve a similar problem of my own. I'm using CASE statements to convert NULL values generated from a JOIN in the overall query.

Particularly, I'm looking at grades, which are housed as DECIMALs within the tables with which I am working.

Here's what I came up with (yes, it's just a snippet of the whole query):

CASE

WHEN B.[DecimalGrade] IS NULL THEN CAST ('' AS NVARCHAR (1))

ELSE CAST (B.[DecimalGrade] AS NVARCHAR (4))

END AS DecimalGrade

SQL Server Convert in Case Statement

2 Answers2