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?

Answer 1

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.