Arithmetic overflow error converting varchar to data type numeric

2
votes

Hi i have a query below which creates insert script for me. the column TotalPremiumValue has datatype Decimal(5,2). when i execute my query i am getting the following error: Arithmetic overflow error converting varchar to data type numeric. can anybody help me?

SELECT 'IF NOT EXISTS(SELECT 1 FROM Lkup.TotalPremium WHERE [TotalPremiumValue]= '''+[TotalPremiumValue]+''') 
INSERT INTO Lkup.TotalPremium ' + 
 '(' + 
 '[TotalPremiumValue],' + 
 '[EffectiveDate]' + 
 ')' + 
 'VALUES (' +
 CASE WHEN [TotalPremiumValue] IS NULL THEN 'NULL' ELSE CONVERT(VARCHAR(40), [TotalPremiumValue]) END + ', ' +
 CASE WHEN [EffectiveDate] IS NULL THEN 'NULL' ELSE 'CONVERT(DATETIME, ' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), [EffectiveDate])) + ')' END + ', ' +
 ')'
 FROM Lkup.TotalPremium

Thanks

2
sql
I imagine a few details, like the contents of the row which is generating the error, would be useful. Also which database you are using. Ideally you could have a smaller testcase (eg. select convert(varchar(40), 'blah'); or whatever causes it to happen - Seth Robertson
Cool. i just used CONVERT(VARCHAR(40),TotalPremiumValue in select and it worked. Thanks Seth. - Programmer

2 Answers

0
votes

Perhaps try recasting the result as a decimal(5,2)? I added the call into a portion of your code and pasted the section below. You could also shrink that varchar(40) down to a varchar(6), which would hold all 5 numbers plus the decimal point. 

 'VALUES ( CAST(' + 
 CASE WHEN [TotalPremiumValue] IS NULL THEN 'NULL' ELSE CONVERT(VARCHAR(40), [TotalPremiumValue]) END + ' AS DECIMAL(5,2)), ' + 
 CASE WHEN [EffectiveDate] IS NULL THEN 'NULL' ELSE 'CONVERT(DATETIME, ' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), [EffectiveDate])) + ')' END + ', ' + 
 ')'
0
votes

Please try the below query

SELECT 
'IF  EXISTS(SELECT 1 FROM Lkup WHERE [TotalPremiumValue] = ' + CONVERT(varchar(50),     [TotalPremiumValue])  + ') 
INSERT INTO Lkup ' + 
'(' + 
'[TotalPremiumValue]' + 
')' + 
'VALUES (' + 
case when CONVERT(varchar(50),[TotalPremiumValue]) IS NULL then 'null' else CONVERT(varchar(50),TotalPremiumValue) end +
')' 
FROM  Lkup