Solved

Arithmetic overflow error converting expression to data type int

Posted on 2011-02-14
21
861 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
Comment Utility
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
Comment Utility
Largest "int" is 2147483648.

Try datatype "bigint" instead.
0
 

Author Comment

by:danielivanov2
Comment Utility
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
 

Author Comment

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

Expert Comment

by:chrismc
Comment Utility
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]
Comment Utility
>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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok, and how should I prevent it from trying to make it numeric?
0
 
LVL 18

Expert Comment

by:chrismc
Comment Utility
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 18

Expert Comment

by:chrismc
Comment Utility
Correction ...

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

... sorry
0
 

Author Comment

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

Expert Comment

by:chrismc
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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]
Comment Utility
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
Comment Utility
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]
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

728 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now