eric eric - 2 years ago 73
SQL Question

SQL case when with 2 different types of output


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 Source
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.


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

    ,CASE WHEN c3 is null THEN CAST(c2 AS sql_variant) else c1 end
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download