sql server 2008 - Arithmetic overflow error converting varchar to data type numeric - Can't find answer that fits -

been searching hour , can't find answer addresses what's happening code. query happily returns data:

with results_test_component_cte(requisitionnumber, resultsname, resultvalue) ( select [requisitionnumber]       ,[resultsname]       ,ltrim(rtrim([resultvalue])) dbo.results_test_component --where testgroupnumber in ('t4367', 't8033') substring(requisitionnumber, 2, 4) = '1521' , resultsname = 'hemoglobin a1c' , isnumeric(resultvalue) = 1 ) , results_test_component_cte2(requisitionnumber, resultsname, resultvalue) ( select requisitionnumber       ,resultsname       ,cast(resultvalue decimal(3,1)) resultvalue results_test_component_cte ) select * results_test_component_cte2 

here first few rows of data, expected:

requisitionnumber   resultsname     resultvalue c1521020510         hemoglobin a1c  5.9 c1521044250         hemoglobin a1c  5.4 c1521123010         hemoglobin a1c  5.6 c1521121420         hemoglobin a1c  5.8 c1521102210         hemoglobin a1c  13.2 

however, when change last line this, throws "arithmetic overflow error converting varchar data type numeric." error:

select * results_test_component_cte2  cast(resultvalue decimal(3,1)) between 7.5 , 10 

i error if leave out "cast" , put where resultvalue between 7.5 , 10. have confirmed data in resultvalue column numeric in format 00.0. need clause fulfill requirement value >=7.5 , <=10. expected return last row resultvalue 13.2, instead error. can tell me problem is?

(i error if use first cte.)

there 2 problems here, first because passes isnumeric not mean can convert decimal. following return 1:

select  isnumeric('1,0'); 

but return error:

select cast('1,0' decimal(3, 1)) 

the other, less obvious problem have no control on when sql server perform operation, though have isnumeric(resultvalue) = 1 in first cte, there no guarantee executed before where cast(resultvalue decimal(3,1)) between 7.5 , 10. still trying (and failing) cast non numeric values decimal. works when in select because evaluated after where.

there 2 ways around this, latter hack. preferable way around force materialisation of first query using temporary table:

create table #resultscomponent (     [requisitionnumber] varchar(50),     [resultsname] varchar(50),     resultvalue decimal(3, 1) ); insert #resultscomponent (requisitionnumber, resultsname, resultvalue) select [requisitionnumber]       ,[resultsname]       ,ltrim(rtrim([resultvalue])) dbo.results_test_component --where testgroupnumber in ('t4367', 't8033') substring(requisitionnumber, 2, 4) = '1521' , resultsname = 'hemoglobin a1c' , isnumeric(resultvalue) = 1;  select  requisitionnumber, resultsname, resultvalue    #resultscomponent   resultvalue between 7.5 , 10; 

the other way, hack, , not guaranteed work use top force order of execution, ensure choose value high enough doesn't affect results:

with results_test_component_cte(requisitionnumber, resultsname, resultvalue) ( select top (100000)         [requisitionnumber]       ,[resultsname]       ,ltrim(rtrim([resultvalue])) dbo.results_test_component --where testgroupnumber in ('t4367', 't8033') substring(requisitionnumber, 2, 4) = '1521' , resultsname = 'hemoglobin a1c' , isnumeric(resultvalue) = 1 ) select requisitionnumber       ,resultsname       ,cast(resultvalue decimal(3,1)) resultvalue results_test_component_cte cast(resultvalue decimal(3,1)) between 7.5 , 10; 

we can recreate problem simply:

create table #t (resultvalue varchar(100)) insert #t (resultvalue) select  t.resultvalue    sys.all_objects o         cross apply (values (cast(name varchar(100))), (cast(object_id varchar(100)))) t (resultvalue);  cte (   select  resultvalue = cast(resultvalue int)        #t       isnumeric(resultvalue) = 1 ) select  *    cte    resultvalue between 1 , 10; 

and view estimated plan (can't actual plan due error) , see both predicates (isnumeric , between 1 , 10) evaluated @ same time:

by adding top can change plan, initial table scan filters isnumeric , uses additional step ensure between 1 , 10 applied numeric data:

with cte (   select  top (1000000) resultvalue = cast(resultvalue int)        #t       isnumeric(resultvalue) = 1 ) select  *    cte    resultvalue between 1 , 10;