Select Case Conversion failed when converting the varchar value to data type int

Firstly the following syntax throws the error:

Conversion failed when converting the varchar value to data type int.

 Case
    when #TFs.lTFID = 1161165 then REPLACE(CONVERT(VARCHAR(11), cast([dCreatedUTC] as datetime), 106), ' ', '-')
    when #TFs.lTFID = 1161166 then 'Administrator'
    when #TFs.lTFID = 1161167 then ''
    when #TFs.lTFID = 1161168 then AssetID
    when #TFs.lTFID = 1161169 then ''
    when #TFs.lTFID = 1161170 then ''
    when #TFs.lTFID = 1161172 then ''
    when #TFs.lTFID = 1161173 then ''
    else CAST(#TFs.lTFID as varchar(20))
  End 'Value'

If I comment out all but one of the "when" lines as below it works.

 Case
    when #TFs.lTFID = 1161165 then REPLACE(CONVERT(VARCHAR(11), cast([dCreatedUTC] as datetime), 106), ' ', '-')
/*  when #TFs.lTFID = 1161166 then 'Administrator'
    when #TFs.lTFID = 1161167 then ''
    when #TFs.lTFID = 1161168 then AssetID
    when #TFs.lTFID = 1161169 then ''
    when #TFs.lTFID = 1161170 then ''
    when #TFs.lTFID = 1161172 then ''
    when #TFs.lTFID = 1161173 then ''*/
    else CAST(#TFs.lTFID as varchar(20))
  End 'Value'

Any thoughts about how I can format this query so that all "cases" work?

Solution

Based on the error message, the result of the CASE expressions gets converted to an INT. Also all of the return values of your WHENs is known to be VARCHAR, except for AssetID, so I assume this must be an INT value.

The error happens because when using a CASE expression, all return values must be of the same data type. In case they have different data types, all values are converted to the type with a higher data type precedence.

And since INT has a higher precedence than VARCHAR, the results are attempted to be converted to an INT which caused the error. To fix this, you have to convert AssetID to VARCHAR(n) also

Case
    when #TFs.lTFID = 1161165 then REPLACE(CONVERT(VARCHAR(11), cast([dCreatedUTC] as datetime), 106), ' ', '-')
    when #TFs.lTFID = 1161166 then 'Administrator'
    when #TFs.lTFID = 1161167 then ''
    when #TFs.lTFID = 1161168 then CAST(AssetID AS VARCHAR(20))
    when #TFs.lTFID = 1161169 then ''
    when #TFs.lTFID = 1161170 then ''
    when #TFs.lTFID = 1161172 then ''
    when #TFs.lTFID = 1161173 then ''
    else CAST(#TFs.lTFID as varchar(20))
End 'Value'