sql - "Error converting data type nvarchar to float" with CASE WHEN Statement -

i'm trying run query following 1 of select statements, , keep getting error "error converting data type nvarchar float." i've been converting vba iif statements cases , can't seem conversions right. fld2 nvarchar(15) , fld1 float data type. need pinpointing why error being thrown.

 case when (isnumeric([fld2]) = 1) round(convert(nvarchar,[fld2]) +     ' / ' + convert(nvarchar,[fld1]),(len(convert(nvarchar,[thedata])) -   charindex(convert(nvarchar, [fld2]),'.'))) else [fld2] end, 

as is, example produce quite funny expression sql server evaluate. let's substitute values fld1, fld2, , thedata example see you're trying do:

[fld1]    = 42.0 [fld2]    = n'69.56' [thedata] = n'something' 

^{(an n before string makes nvarchar instead of varchar)}

with substitutions, resulting query this:

case when (isnumeric(n'69.56') = 1)      round(convert(nvarchar,'69.56') + ' / ' + convert(nvarchar, 42.0),         (len(convert(nvarchar,'something')) - charindex(convert(nvarchar, n'69.56'),'.')))   else      n'69.56'  end 

since don't need convert nvarchar nvarchar explicitly, query looks more like:

case when (isnumeric(n'69.56') = 1)      round(n'69.56 / ' + convert(nvarchar, 42.0),         (len(n'something') - charindex(n'69.56','.')))   else      n'69.56' end 

so there couple of problems:

1. you're passing varchar value round() function, expects numeric value, not expression
2. the 2 paths of case statement returning different types

what think query should is:

case when isnumeric([fld2]) = 1      convert(nvarchar, round(convert(float, [fld2]) / [fld1],         (len([thedata]) - charindex([fld2], '.')))) else      [fld2] end 

the above math , rounding on numeric results instead of strings, doesn't unnecessary conversions, , returns same type in both cases.