[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 909
  • Last Modified:

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

0
danielivanov2
Asked:
danielivanov2
  • 9
  • 5
  • 3
  • +3
1 Solution
 
mkobrinCommented:
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
 
sjklein42Commented:
Largest "int" is 2147483648.

Try datatype "bigint" instead.
0
 
danielivanov2Author Commented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
danielivanov2Author Commented:
if I execute the procedure with parameter value "x1234567890123" instead of "1234567890123", everything works fine
0
 
chrismcCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
danielivanov2Author Commented:
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
 
chrismcCommented:
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
 
danielivanov2Author Commented:
Ok, and how should I prevent it from trying to make it numeric?
0
 
chrismcCommented:
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
 
chrismcCommented:
Correction ...

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

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

So its function issue
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
danielivanov2Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
SharathData EngineerCommented:
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
 
danielivanov2Author Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now