[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Arithmetic overflow error converting expression to data type int

Posted on 2011-02-14
21
Medium Priority
?
902 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 143

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 41

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 143

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 143

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 41

Accepted Solution

by:
Sharath earned 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

656 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