The problem is that you have two different return types, depending on the value of
[GenderIsMale]; the two
WHEN conditions return a string while the
ELSE returns a
BIT. It is easier to see if you format the code nicely:
when CAST([GenderIsMale] AS bit)=1 then 'Male' -- return type = VARCHAR
when CAST([GenderIsMale] AS bit)=0 then 'Female' -- return type = VARCHAR
else CAST([GenderIsMale] AS bit) -- return type = BIT
You can test this as follows:
DECLARE @GenderIsMale INT = 1;
when CAST(@GenderIsMale AS bit)=1 then 'Male'
when CAST(@GenderIsMale AS bit)=0 then 'Female'
else CAST(@GenderIsMale AS bit)
Msg 245, Level 16, State 1, Line 28
Conversion failed when converting the varchar value 'Male' to data type bit.
You can fix this by returning a string (or literal
NULL) in the
ELSE condition (pick one of those
ELSE conditions, depending on what you are trying to accomplish):
SELECT CASE CAST([GenderIsMale] AS BIT)
WHEN 1 THEN 'Male'
WHEN 0 THEN 'Female'
ELSE 'other string'
--ELSE CONVERT(VARCHAR(50), [GenderIsMale]) -- if column is a numeric type
--ELSE [GenderIsMale] -- if column is a string type
Depending on the datatype of the
GenderIsMale column and the data in it, it is possible that there is an additional source of the error: the
CAST of the value into
BIT. For example, if the datatype is
VARCHAR, then values of empty string,
'false', and strings of just digits 0 - 9 (no spaces, commas, periods, or minus sign) will convert, but anything else will error. Since you are using SQL Server 2012, you can switch to using
TRY_CAST (or even
TRY_CONVERT) to get around that.
ELSE conditions are mainly to show the possible variations for getting the resulting expression to be consistent across all branches of the
CASE statement. But in the scenario here of having a
BIT / boolean value, if the datatype of the
GenderIsMale column is a number, then the only value that could fall through to the
ELSE is a
NULL, hence you would use
ELSE NULL. But if the datatype is a string, then you would use