Problems with comma seperated parameter list to non char table

• Ken Gaul

  Default port

  Points: 1422

  More actions

  January 6, 2010 at 9:52 am

  #214171

  So I don't want to start another debate about the best way of doing the whole comma seperated list to table thing there appear to be plenty of discussion already about that. For various reasons I would like to use the tally table CTE method as so but I seem to having issues with type conversion...

  create FUNCTION dbo.fn_convert_comma_to_table_decimal (@String VARCHAR(max))

  returns table as

  return

  ( WITH Numbers AS (

  SELECT TOP(len(@String)+1)

  ROW_NUMBER() OVER (ORDER BY t1.Number) AS N

  FROM master..spt_values t1

  CROSS JOIN master..spt_values t2

  CROSS JOIN master..spt_values t3

  )

  select distinct cast(substring(@String, n, charindex(',', @String + ',', n) - n) as decimal(10,5)) s

  from Numbers

  where n <= convert(int, len(@String)) and

  substring(',' + @String, n, 1) = ','

  )

  A simple select on this works just fine, e.g

  select len(s),s from dbo.fn_convert_comma_to_table_decimal('1.2,11.1,1,1,1.1')

  Sadly whenever I try to use it to join to another table or place a where clause restriction I get nasty conversion errors. Can anyone think of what is going on here, and if it is possible to work around it?

  select len(s),s from dbo.fn_convert_comma_to_table_decimal('1.2,11.1,1,1,1.1')

  where s > 1

  Msg 8114, Level 16, State 5, Line 1

  Error converting data type varchar to numeric.

  Thanks

  Ken

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  January 6, 2010 at 5:46 pm

  #1099984

  the execution plan says it all:

  With the WHERE clause:

  |--Filter(WHERE: (CONVERT(decimal(10,5),substring(CONVERT_IMPLICIT(varchar(max),'1.2,11.1,1,1,1.1',0),[Expr1006],charindex(',',CONVERT_IMPLICIT(varchar(max),'1.2,11.1,1,1,1.1',0)+',',[Expr1006])-[Expr1006]),0)>(1.00000) AND [Expr1006]<=CONVERT_IMPLICIT(bigint,CONVERT(int,len(CONVERT_IMPLICIT(varchar(max),'1.2,11.1,1,1,1.1',0)),0),0) AND substring(','+CONVERT_IMPLICIT(varchar(max),'1.2,11.1,1,1,1.1',0),[Expr1006],(1))=','))

  ---

  Without the WHERE clause:

  |--Filter(WHERE: ([Expr1006]<=CONVERT_IMPLICIT(bigint,CONVERT(int,len(CONVERT_IMPLICIT(varchar(max),'1.2,11.1,1,1,1.1',0)),0),0) AND substring(','+CONVERT_IMPLICIT(varchar(max),'1.2,11.1,1,1,1.1',0),[Expr1006],(1))=','))

  The "s>1" predicate is being "inlined" into the string splitting function and failing because some partial strings are not numeric during the processing.

  Fix:???? Maybe output the results into a table and then query for "s>1" on that table?

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]