Solved

Arithmetic overflow error converting expression to data type int

Posted on 2011-02-14
21
868 Views
Last Modified: 2012-05-11
Hello, I have a sql procedure (sql server 2005) that returns a dataset based a parameter input (social security number). Everything works well until I pass 13 digits number (SSN for a private person). The error "Arithmetic overflow error converting expression to data type int." is thrown.
Attached the sql procedure code. Thanks!
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[prc_csCaseSummaryListbySSN]
	-- Add the parameters for the stored procedure here
	@SsnList varchar(500)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT 
		c.CaseId,
		c.CaseChannel + '-' + t.CaseType AS TipCaz,
		s.StatusName CurrentStatus,
		SUBSTRING(CONVERT(varchar,c.CurrentStatusDate,103),1,10) AS CurrentStatusDate,				
		cd.CustomerId,
		cd.CompanyName,
		cd.SSN,
		cd.TotalSAI,			
		u.UserName
	FROM 
		dbo.tblCases c,
		dbo.tblCasesTypes t,
		dbo.tblCasesStatuses s,
		dbo.tblAppUsers u,
		dbo.tblCasesCustomerDetails cd,
		fn_ParseText2Table(@SsnList,',') f
	WHERE 
		c.CurrentUserId=u.UserId
		AND c.CaseTypeId=t.CaseTypeId
		AND c.CurrentStatusId=s.StatusId
		AND c.CaseId=cd.CaseId
		AND CONVERT(varchar(500),cd.SSN)=f.txt_value
		--AND c.CurrentStatus NOT LIKE '%Rezolvat%'			
	ORDER BY 
		--SUBSTRING(CONVERT(varchar,c.CurrentStatusDate,103),1,10) ASC
		c.CurrentStatusDate DESC

fn_ParseText2Table function definition:
ALTER   function [dbo].[fn_ParseText2Table] 
 (
 @p_SourceText  varchar(8000)
 ,@p_Delimeter varchar(100) = ',' --default to comma delimited.

 )
RETURNS @retTable TABLE 
 (
  Position  int identity(1,1)
 ,Int_Value int 
 ,Num_value Numeric(18,3)
 ,txt_value varchar(2000)
 )
AS
/*
********************************************************************************
Purpose: Parse values from a delimited string
  & return the result as an indexed table
********************************************************************************
*/
BEGIN
 DECLARE @w_Continue  int
  ,@w_StartPos  int
  ,@w_Length  int
  ,@w_Delimeter_pos int
  ,@w_tmp_int  int
  ,@w_tmp_num  numeric(18,3)
  ,@w_tmp_txt   varchar(2000)
  ,@w_Delimeter_Len tinyint
 if len(@p_SourceText) = 0
 begin
  SET  @w_Continue = 0 -- force early exit

 end 
 else
 begin
 -- parse the original @p_SourceText array into a temp table

  SET  @w_Continue = 1
  SET @w_StartPos = 1
  SET @p_SourceText = RTRIM( LTRIM( @p_SourceText))
  SET @w_Length   = DATALENGTH( RTRIM( LTRIM( @p_SourceText)))
  SET @w_Delimeter_Len = len(@p_Delimeter)
 end
 WHILE @w_Continue = 1
 BEGIN
  SET @w_Delimeter_pos = CHARINDEX( @p_Delimeter
      ,(SUBSTRING( @p_SourceText, @w_StartPos
      ,((@w_Length - @w_StartPos) + @w_Delimeter_Len)))
      )
 
  IF @w_Delimeter_pos > 0  -- delimeter(s) found, get the value

  BEGIN
   SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos 
        ,(@w_Delimeter_pos - 1)) ))
   if isnumeric(@w_tmp_txt) = 1
   begin
    set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as int)
    set @w_tmp_num = cast( @w_tmp_txt as numeric(18,3))
   end
   else
   begin
    set @w_tmp_int =  null
    set @w_tmp_num =  null
   end
   SET @w_StartPos = @w_Delimeter_pos + @w_StartPos + (@w_Delimeter_Len- 1)
  END
  ELSE -- No more delimeters, get last value

  BEGIN
   SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos 
      ,((@w_Length - @w_StartPos) + @w_Delimeter_Len)) ))
   if isnumeric(@w_tmp_txt) = 1
   begin
    set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as int)
    set @w_tmp_num = cast( @w_tmp_txt as numeric(18,3))
   end
   else
   begin
    set @w_tmp_int =  null
    set @w_tmp_num =  null
   end
   SELECT @w_Continue = 0
  END
  INSERT INTO @retTable VALUES( @w_tmp_int, @w_tmp_num, @w_tmp_txt )
 END
RETURN
END

Open in new window

0
Comment
Question by:danielivanov2
  • 9
  • 5
  • 3
  • +3
21 Comments
 
LVL 7

Expert Comment

by:mkobrin
ID: 34894576
The reason for this is that the max value you can use for an int is 2,147,483,647
if you want to use a 13 digit number and save it as an int you will have to use a bigint data type and not an int data type
0
 
LVL 16

Expert Comment

by:sjklein42
ID: 34894584
Largest "int" is 2147483648.

Try datatype "bigint" instead.
0
 

Author Comment

by:danielivanov2
ID: 34894585
all values are supposed to be varchar. not int..
the join with my tablescalar function is with "txt_value", that is defined as varchar(2000), regardless that my data is in number format (I convert it to varchar when joining with function)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:danielivanov2
ID: 34894591
if I execute the procedure with parameter value "x1234567890123" instead of "1234567890123", everything works fine
0
 
LVL 18

Expert Comment

by:chrismc
ID: 34894611
I may be wrong but I think the issue is in this area;

   if isnumeric(@w_tmp_txt) = 1
   begin
    set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as int)

It's going to try and cast the 13 digit numeric SSN to an Int. Change the Cast to be a BigInt and see if that works?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34894621
>set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as int)

will fail indeed for "big ints" ... and there is no error handler about it.
you shall need to specify as argument to the function that defines which output field(s) to populate ...
0
 

Author Comment

by:danielivanov2
ID: 34894635
not working, with either Int_Value defined as bigint and/or set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as bigint) (new function code attached)
USE [pricingtools]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_ParseTest]    Script Date: 02/15/2011 10:07:59 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER   function [dbo].[fn_ParseTest] 
 (
 @p_SourceText  varchar(8000)
 ,@p_Delimeter varchar(100) = ',' --default to comma delimited.

 )
RETURNS @retTable TABLE 
 (
  Position  int identity(1,1)
 ,Int_Value bigint 
 ,Num_value Numeric(18,3)
 ,txt_value varchar(2000)
 )
AS
/*
********************************************************************************
Purpose: Parse values from a delimited string
  & return the result as an indexed table
 Reworked to allow for delimiters > 1 character in length 
 and to convert Text values to numbers
********************************************************************************
*/
BEGIN
 DECLARE @w_Continue  int
  ,@w_StartPos  int
  ,@w_Length  int
  ,@w_Delimeter_pos int
  ,@w_tmp_int  int
  ,@w_tmp_num  numeric(18,3)
  ,@w_tmp_txt   varchar(2000)
  ,@w_Delimeter_Len tinyint
 if len(@p_SourceText) = 0
 begin
  SET  @w_Continue = 0 -- force early exit

 end 
 else
 begin
 -- parse the original @p_SourceText array into a temp table

  SET  @w_Continue = 1
  SET @w_StartPos = 1
  SET @p_SourceText = RTRIM( LTRIM( @p_SourceText))
  SET @w_Length   = DATALENGTH( RTRIM( LTRIM( @p_SourceText)))
  SET @w_Delimeter_Len = len(@p_Delimeter)
 end
 WHILE @w_Continue = 1
 BEGIN
  SET @w_Delimeter_pos = CHARINDEX( @p_Delimeter
      ,(SUBSTRING( @p_SourceText, @w_StartPos
      ,((@w_Length - @w_StartPos) + @w_Delimeter_Len)))
      )
 
  IF @w_Delimeter_pos > 0  -- delimeter(s) found, get the value

  BEGIN
   SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos 
        ,(@w_Delimeter_pos - 1)) ))
   if isnumeric(@w_tmp_txt) = 1
   begin
    set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as bigint)
    set @w_tmp_num = cast( @w_tmp_txt as numeric(18,3))
   end
   else
   begin
    set @w_tmp_int =  null
    set @w_tmp_num =  null
   end
   SET @w_StartPos = @w_Delimeter_pos + @w_StartPos + (@w_Delimeter_Len- 1)
  END
  ELSE -- No more delimeters, get last value

  BEGIN
   SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos 
      ,((@w_Length - @w_StartPos) + @w_Delimeter_Len)) ))
   if isnumeric(@w_tmp_txt) = 1
   begin
    set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as bigint)
    set @w_tmp_num = cast( @w_tmp_txt as numeric(18,3))
   end
   else
   begin
    set @w_tmp_int =  null
    set @w_tmp_num =  null
   end
   SELECT @w_Continue = 0
  END
  INSERT INTO @retTable VALUES( @w_tmp_int, @w_tmp_num, @w_tmp_txt )
 END
RETURN
END

Open in new window

0
 
LVL 18

Expert Comment

by:chrismc
ID: 34894793
You may be sending it a varchar but you are testing to see if the contents of the varchar are numeric and because it is then it converts it to Int (now BigInt).

The fact it works with an "X" in front proves the point that without it it is definitely trying to make it numeric.
0
 

Author Comment

by:danielivanov2
ID: 34894797
Ok, and how should I prevent it from trying to make it numeric?
0
 
LVL 18

Expert Comment

by:chrismc
ID: 34894910
Well it's down to the purpose of that parsing function.

In this scenario, the simple solution would be to take out the "IsNumeric" portion of code and treat everything as text.

But I suspect this is used more widely and that you can do this.

I actually have a routine like that to split multi-valued strings into tables but I have 2 versions, one specifically for strings and one specifically for integers.

In your scenario I would be calling the string version. Forcing the issue at design time.
The only difference between the 2 versions is that one converts stuff to int and the other only tries to convert to varchar.


0
 
LVL 18

Expert Comment

by:chrismc
ID: 34894912
Correction ...

... But I suspect this is used more widely and that you CAN'T do this.

... sorry
0
 

Author Comment

by:danielivanov2
ID: 34894989
the function will be used for strings like:
RO1234
1234567890123
1,123,234,345
0
 
LVL 18

Expert Comment

by:chrismc
ID: 34895196
It appears it's already designed to do that. But you have to take out the IsNumeric() section of code.

Just create a copy of your function, rename it and take out lines 67-76 and it will treat all the items as strings.
0
 

Author Comment

by:danielivanov2
ID: 34895744
in order to avoid the error, I needed to comment both sections where bigint appears
but in this case the procedure returns nothing, running without conversion error...
0
 
LVL 40

Expert Comment

by:Sharath
ID: 34902002
Is this the issue with function or SP? First run these sample queries to check if it is problem with function?
select * from fn_ParseText2Table('1234567890123',',')
select * from fn_ParseText2Table('RO1234',',')
select * from fn_ParseText2Table('1,123,234,345',',')
0
 

Author Comment

by:danielivanov2
ID: 34904471
First: arithmetic overflow error
Second: ok, returns 1 row
Third: ok, returns 4 rows

So its function issue
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34905015
well, let me give you this advice:

create the function, as you have it, once returning only varchar data.
then, a second function that uses the first one, and which casts the data into numerical as needed.
I have created my own function dbo.ParmsToList:
http://www.experts-exchange.com/A_1536.html
which basically matches your function [dbo].[fn_ParseText2Table]

you could then create a function to return numerical (int) data like this:

CREATE function [dbo].[fn_ParseText2IntTable] (@data varchar(max), @delimiter varchar(100))
returns @result TABLE (Value int, row_num int identity )
as
begin
   insert into @result ( value )
     select cast(x as int)
   from (
     select cast(x as decimal(30)) y
   select case when isnumeric(value) = 0 then null 
          else value end x
     from dbo.ParmsToList(@data, @delimiter )
  ) sq 
  where sq.value is not null
     and cast(x as decimal(30)) < cast(2147483647 as decimal(30))
 ) sq2
 return     
end 

Open in new window

0
 

Author Comment

by:danielivanov2
ID: 34907542
Msg 156, Level 15, State 1, Procedure fn_ParseTest2, Line 15
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Procedure fn_ParseTest2, Line 18
Incorrect syntax near ')'.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34907959
oups ...
CREATE function [dbo].[fn_ParseText2IntTable] (@data varchar(max), @delimiter varchar(100))
returns @result TABLE (Value int, row_num int identity )
as
begin
 insert into @result ( value )
 select cast(x as int)
   from (
     select cast(x as decimal(30)) y
       from (
         select case when isnumeric(value) = 0 then null else value end x
           from dbo.ParmsToList(@data, @delimiter ) f
            ) sq 
      where sq.value is not null
        and cast(x as decimal(30)) < cast(2147483647 as decimal(30))
    ) sq2
 return     
end

Open in new window

0
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 34912132
Can you try this function?
CREATE FUNCTION [dbo].[fn_Split](@text nvarchar(max), @delimiter char(1) = ' ')
RETURNS @Strings TABLE (position int IDENTITY PRIMARY KEY, value nvarchar(max)) AS 
  BEGIN 
DECLARE @index int
    SET @index = -1
  WHILE (LEN(@text) > 0)
  BEGIN
    SET @index = CHARINDEX(@delimiter , @text)
     IF (@index = 0) AND (LEN(@text) > 0)
  BEGIN 
 INSERT INTO @Strings VALUES (@text)
  BREAK 
    END
     IF (@index > 1)
  BEGIN
 INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
    SET @text = RIGHT(@text, (LEN(@text) - @index))
    END 
   ELSE 
    SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
 RETURN
    END
 GO

Open in new window

I checked with three sample data and got result.
select * from dbo.fn_SPlit('1234567890123',',')
select * from dbo.fn_SPlit('RO1234',',')
select * from dbo.fn_SPlit('1,123,234,345',',')

Open in new window

0
 

Author Closing Comment

by:danielivanov2
ID: 34913868
This function works ok. Meanwhile I have found a workaround, to modify this kind of data (numeric, 13 length) by adding a string, but in cases when this workaround is not accepted the function is quite ok. Thanls!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Stored procedure 4 32
configure service broker on all databases 2 82
SQL Backup skipping a few tables 7 44
SQL query with cast 38 43
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question