c1 c2 c3
'a1' 1 'hi'
'b1' 2 'hee'
'c1' 3 null
select case when c3 is null then c2 else c1 end from Table
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
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