Link to home
Start Free TrialLog in
Avatar of danielivanov2
danielivanov2Flag for Romania

asked on

Arithmetic overflow error converting expression to data type int

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

Avatar of mkobrin
mkobrin

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
Largest "int" is 2147483648.

Try datatype "bigint" instead.
Avatar of danielivanov2

ASKER

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)
if I execute the procedure with parameter value "x1234567890123" instead of "1234567890123", everything works fine
Avatar of Chris McGuigan
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?
>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 ...
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

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.
Ok, and how should I prevent it from trying to make it numeric?
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.


Correction ...

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

... sorry
the function will be used for strings like:
RO1234
1234567890123
1,123,234,345
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.
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...
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',',')
First: arithmetic overflow error
Second: ok, returns 1 row
Third: ok, returns 4 rows

So its function issue
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:
https://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

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 ')'.
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

ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!