implicit conversion failure varchar to numeric using DelimitedSplit8K function

• rossa

  SSC Enthusiast

  Points: 133

  More actions

  May 18, 2015 at 8:18 am

  #298165

  We're using Jeff Moden's DelimitedSplit8K table valued function published in the following article http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]

  Our system vendor is upgrading from SQL 2000 to SQL 2014 and we have found that an implicit conversion error occurs when converting varchar to numeric using the DelimitedSplit8K function in the where clause. Using the following code, the DelimitedSplit8K function works without error on the existing SQL 2000 database but not on the new SQL 2014 database (collation is the same on both databases Latin1_General_CI_AS). Before we update all the SSRS reports and SQL objects that use the DelimitedSplit8K function in the where clause I'm keen to understand if there is a SQL configuration option we've overlooked that will keep it converting varchar to numeric implicitly.

  set nocount on

  --declare a table variable to store some made up data about organisation invoices

  --note the data type of the org_id field is numeric(10,0)

  declare @invoice table (inv_id int,inv_amt money,org_id numeric(10,0))

  --populate the table variable with some made up data to test with

  --the table contains invoice data for organisations 1, 2 and 3

  insert into @invoice values (1,10987.89,1)

  insert into @invoice values (2,90978.00,3)

  insert into @invoice values (3,11786.67,2)

  insert into @invoice values (4,78096.12,2)

  --declare and set a variable to store a comma separated list of organisation ids

  declare @org_list varchar(8000)

  set @org_list = '2,3'

  --use the comma separated list of org_id as criteriaa

  select*

  from@invoice i

  wherei.org_id in (select item from BH.DelimitedSplit8K(@org_list,','))

  The function works fine on SQL 2000 and SQL 2005 but in SQL 2008 and SQL 2014 I get the following error message:

  Msg 8114, Level 16, State 5, Line 12

  Error converting data type varchar to numeric.

  What am I missing? Is there something I can do to the DelimitedSplit8K function or is there a SQL configuration issue I need to resolve?

  Casting the org_id field in the example above to an integer works but I would need to do this in many SQL objects and SSRS reports. The following MSDN article suggests conversion from varchar to numeric is implicit https://msdn.microsoft.com/en-us/library/ms191530.aspx

  Interestingly, the implicit conversion is successful when the DelimitedSplit8K function is joined to other tables. For example:

  --declare and set a variable to store a comma separated list of organisation ids

  declare @org_list varchar(8000)

  set @org_list = '2,3'

  --use the comma separated list of org_id as criteriaa

  select*

  from@invoice i

  inner joinBH.DelimitedSplit8K(@org_list,',') ds

  oni.org_id = ds.Item

  Any help greatly appreciated.

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  May 18, 2015 at 8:41 am

  #1798648

  Make org_id an int.

  declare @invoice table (inv_id int,inv_amt money,org_id int /*numeric(10,0)*/)

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  May 18, 2015 at 8:59 am

  #1798662

  There is something missing here for sure. You stated that you use the DelimitedSplit8K function in your 2000 instance. In the original format it would NOT work in 2000 because the are a number of ctes.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 18, 2015 at 9:52 am

  #1798682

  Compare the estimated plans for the following statements:

  SELECT i.*

  FROM @invoice i

  WHERE i.org_id in (select item from dbo.DelimitedSplit8K(@org_list,','))

  SELECT i.*

  FROM @invoice i

  INNER JOIN dbo.DelimitedSplit8K(@org_list,',') d ON d.item = i.org_id

  In the first plan, determination of the comma positions and of matches between the two table sources are both performed in the two filters, nodeID 3 and 7. Here's the predicate for nodeID 7:

  @invoice.[org_id] as [org_id]=CONVERT_IMPLICIT(numeric(10,0),substring([@org_list],CONVERT_IMPLICIT(int,[Expr1053]+(1),0),CONVERT_IMPLICIT(int,isnull(CONVERT_IMPLICIT(bigint,

  CASE

  WHEN charindex(',',[@org_list],CONVERT_IMPLICIT(int,[Expr1053]+(1),0))=(0) THEN NULL

  ELSE charindex(',',[@org_list],CONVERT_IMPLICIT(int,[Expr1053]+(1),0)) END

  ,0)-([Expr1053]+(1)),(8000)),0)),0)

  AND substring([@org_list],CONVERT_IMPLICIT(int,[Expr1053],0),(1))=','

  What this means is SQL Server will attempt to convert all of the characters in the string to numeric(10,0) then filter out the commas.

  In the second plan, the comma positions are determined in the filter, nodeID = 4 and the match between the two table sources appears as the predicate in the nested loops join. Here's the predicate:

  CONVERT_IMPLICIT(numeric(10,0),

  substring([@org_list],CONVERT_IMPLICIT(int,[Union1055],0),CONVERT_IMPLICIT(int,isnull(CONVERT_IMPLICIT(bigint,

  CASE

  WHEN charindex(',',[@org_list],CONVERT_IMPLICIT(int,[Union1055],0))=(0) THEN NULL

  ELSE charindex(',',[@org_list],CONVERT_IMPLICIT(int,[Union1055],0)) END

  ,0)-[Union1055],(8000)),0)),0)=@invoice.[org_id] as .[org_id]

  Union1055 = n+1, where n is a comma position. No attempt is made to convert commas to numeric.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  May 18, 2015 at 10:01 am

  #1798689

  Sean Lange (5/18/2015)

  ---

  There is something missing here for sure. You stated that you use the DelimitedSplit8K function in your 2000 instance. In the original format it would NOT work in 2000 because the are a number of ctes.

  I was thinking the same thing.

  Rossa,

  Could you share the 2000 friendly version for the DelimitedSplit8k function?

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 18, 2015 at 10:11 am

  #1798695

  Sean Lange (5/18/2015)

  ---

  There is something missing here for sure. You stated that you use the DelimitedSplit8K function in your 2000 instance. In the original format it would NOT work in 2000 because the are a number of ctes.

  Doesn't SQL Server 2000 also predate iTVF's?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  May 18, 2015 at 10:18 am

  #1798700

  ChrisM@Work (5/18/2015)

  ---

  Sean Lange (5/18/2015)

  ---

  There is something missing here for sure. You stated that you use the DelimitedSplit8K function in your 2000 instance. In the original format it would NOT work in 2000 because the are a number of ctes.

  Doesn't SQL Server 2000 also predate iTVF's?

  IIRC table valued functions were introduced with sql server 2000 but they got a major upgrade in how they worked in 2005.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 18, 2015 at 10:25 am

  #1798706

  Really interesting. I ran the T-SQL code provided by the OP on my SQL Server 2012 DE and I get the Error converting data type varchar to numeric as well.

  If I change the subquery to (select cast(cast(Item as int) as numeric(10,0)) from dbo.DelimitedSplit8K(@org_list,',')) everything runs.

  Looking at CAST and CONVERT in BOL it says you should be able to implicitly convert varchar to numeric.

  Definitely an interesting issue.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 18, 2015 at 10:26 am

  #1798707

  Same issue when I run it on a SQL Server 2008 R2 SE instance (10.50,6000).

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  May 18, 2015 at 1:46 pm

  #1798797

  ChrisM@Work (5/18/2015)

  ---

  Compare the estimated plans for the following statements:

  SELECT i.*

  FROM @invoice i

  WHERE i.org_id in (select item from dbo.DelimitedSplit8K(@org_list,','))

  SELECT i.*

  FROM @invoice i

  INNER JOIN dbo.DelimitedSplit8K(@org_list,',') d ON d.item = i.org_id

  In the first plan, determination of the comma positions and of matches between the two table sources are both performed in the two filters, nodeID 3 and 7. Here's the predicate for nodeID 7:

  @invoice.[org_id] as [org_id]=CONVERT_IMPLICIT(numeric(10,0),substring([@org_list],CONVERT_IMPLICIT(int,[Expr1053]+(1),0),CONVERT_IMPLICIT(int,isnull(CONVERT_IMPLICIT(bigint,

  CASE

  WHEN charindex(',',[@org_list],CONVERT_IMPLICIT(int,[Expr1053]+(1),0))=(0) THEN NULL

  ELSE charindex(',',[@org_list],CONVERT_IMPLICIT(int,[Expr1053]+(1),0)) END

  ,0)-([Expr1053]+(1)),(8000)),0)),0)

  AND substring([@org_list],CONVERT_IMPLICIT(int,[Expr1053],0),(1))=','

  What this means is SQL Server will attempt to convert all of the characters in the string to numeric(10,0) then filter out the commas.

  In the second plan, the comma positions are determined in the filter, nodeID = 4 and the match between the two table sources appears as the predicate in the nested loops join. Here's the predicate:

  CONVERT_IMPLICIT(numeric(10,0),

  substring([@org_list],CONVERT_IMPLICIT(int,[Union1055],0),CONVERT_IMPLICIT(int,isnull(CONVERT_IMPLICIT(bigint,

  CASE

  WHEN charindex(',',[@org_list],CONVERT_IMPLICIT(int,[Union1055],0))=(0) THEN NULL

  ELSE charindex(',',[@org_list],CONVERT_IMPLICIT(int,[Union1055],0)) END

  ,0)-[Union1055],(8000)),0)),0)=@invoice.[org_id] as .[org_id]

  Union1055 = n+1, where n is a comma position. No attempt is made to convert commas to numeric.

  Spot on, in my opinion.

  As for the solution, well... relying on an implicit conversion is not really a good idea, so you could try casting [item] to the right numeric type as seemed to work for Lynn, but is that guaranteed? I doubt it...

  This also seems to work, but again, would not be guaranteed to always work:

  selecti.*

  from@invoice i

  join dbo.DelimitedSplit8K(@org_list,',') as k

  on i.org_id = k.item

  My advice would be to either use a TVP instead of a string, or store the results of DelimitedSplit8K into a temp table and then join to that temp table.

  Anything else seems a bit untrustworthy.

  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]