eric eric - 5 months ago 16
SQL Question

SQL case when with 2 different types of output

Table

c1 c2 c3
'a1' 1 'hi'
'b1' 2 'hee'
'c1' 3 null


When I do

select case when c3 is null then c2 else c1 end from Table


I get a "Conversion failed when converting" error.

I know that case when can only return 1 type, but is there a way to convert varchar to int/tinyint? If not, is there another way?

Answer
select case when c3 is null then CAST(c2 AS VARCHAR(10)) else c1 end from Table

you are mixing numeric and string data types. you will be required to use as string VARCHAR, CHAR, etc.

By casting your numeric data CAST(c2 AS VARCHAR(10)) you can solve the issue.

I guess for fun and to be more accurate in sql-server you could keep it as an integer but it would require to cast as sql_variant which would then make the entire field sql_variant and require casting for any use thereafter and you would gain 0 benefit because you could always test for IIF(ISNUMERIC(CASE)=1,CAST(value as INT),NULL).... Probably too much for this answer.

DECLARE @Table AS TABLE (C1 CHAR(2), c2 INT, c3 VARCHAR(4))

INSERT INTO @Table (C1, c2, c3)
VALUES ('a1', 1, 'hi'), ('b1',2,'hee'),('c1',3,null)

SELECT *
    ,CASE WHEN c3 is null THEN CAST(c2 AS sql_variant) else c1 end
FROM
    @Table