SQL Server Convert in Case Statement

case, case-statement, sql-server

Solution

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.

Problem

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?

Original source