Arithmetic overflow error converting varchar to data type numeric

Question

Post reply

Arithmetic overflow error converting varchar to data type numeric

chloe82402

SSC Veteran

Points: 297
More actions
March 26, 2010 at 9:10 am

#217077

I get the following error message when I run the below code. "Arithmetic overflow error converting varchar to data type numeric". Please help!
DECLARE @MARKET_ID VARCHAR(MAX)
DECLARE @FUNC_AREA_ID VARCHAR(50)
DECLARE @DATE_ID VARCHAR(8)
DECLARE @GOAL_AMT NUMERIC (18,8)
DECLARE @USER_ID VARCHAR (30)
SET @MARKET_ID = 1
SET @FUNC_AREA_ID = 3
SET @DATE_ID = '20100301'
SET @GOAL_AMT = '1000000'
SET @USER_ID = 'AGPCORP\hbyars1'
--OVERRIDE VALUE OF PARAM IF NOT A SUPER USER
IF (SELECT COUNT(*) FROM fn_DSG_APP_PERMISSION (@USER_ID,9) WHERE PRMSN_ID = 1) = 0---NOT A SUPER USER
DECLARE @MESSAGE_DESC VARCHAR(8000)
SET @MESSAGE_DESC = ''
IF (SELECT COUNT(*) FROM ACCRUAL_SRP.dbo.MEI_AUTOMATION_GOAL-----CHECKING TABLE TO SEE IF ROW ALREADY EXISTS
WHERE RTRIM(MARKET_ID) = RTRIM(@MARKET_ID)
AND RTRIM(FUNC_AREA_ID) = RTRIM(@FUNC_AREA_ID)
AND DATE_ID = (SELECT CAST(@DATE_ID as INT))
AND RTRIM(GOAL_AMT) = RTRIM(@GOAL_AMT)) <> 0
BEGIN
SET @MESSAGE_DESC = 'Unable to add row. MEI already exists.'
END
--------****************************************************************************
IF @MESSAGE_DESC = ''
BEGIN
---SQL TO DO THE ACTUAL ADD TO THE GOAL TABLE
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'INSERT INTO ACCRUAL_SRP.dbo.MEI_AUTOMATION_GOAL VALUES
(' + '''' + @FUNC_AREA_ID + '''' + ',' + '''' + CONVERT(varchar(25),@DATE_ID,121)
+ '''' + ',' + '''' + @GOAL_AMT + '''' + ',' + '''' + @USER_ID + ''''+')'
EXEC (@SQL)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

Can we see the schema of this table please?

ACCRUAL_SRP.dbo.MEI_AUTOMATION_GOAL

---------------------------------------------------------------------------------

chloe82402 SSC Veteran Points: 297 More actions · Answer 2

This is what the fields in the table look like. Thanks

MARKET_ID int

FUNC_AREA_ID int

DATE_ID int

GOAL_AMT numeric(18, 8)

UPDATE_DTTM smalldatetime

UPDATE_USER_ID varchar(30)

darkfour Old Hand Points: 383 More actions · Answer 3

Looks to me like its converting @SQl from a varchar to numeric so that it can add it to the @GOAL_AMT (which is a numeric value). As you're trying to construct a string using the '+' to concatenate the parts together you need to ensure all parts are strings.

This should work for you although I've not tested it

SET @SQL = 'INSERT INTO ACCRUAL_SRP.dbo.MEI_AUTOMATION_GOAL VALUES

(' + '''' + @FUNC_AREA_ID + '''' + ',' + '''' + CONVERT(varchar(25), @DATE_ID, 121)

+ '''' + ',' + CONVERT(VARCHAR(25),@GOAL_AMT) +',' + '''' + @USER_ID + ''''

+ ')'

but if I run it using PRINT @sql I get the following string

INSERT INTO ACCRUAL_SRP.dbo.MEI_AUTOMATION_GOAL VALUES

('3','20100301',1000000.00000000,'AGPCORP\hbyars1')

which looks like it'll work

Nabha SSCrazy Eights Points: 8534 More actions · Answer 4

Precisely as Graham says, its while building the d-SQL that you are having problems.

---------------------------------------------------------------------------------

chloe82402 SSC Veteran Points: 297 More actions · Answer 5

chloe82402

SSC Veteran

Points: 297

March 29, 2010 at 6:23 am

#1141373

It worked!!! Thank you so much for your help!!!!