Arithmetic overflow error converting varchar to data type numeric.

[ideasworking]

Hello,

I could use some help identifying a problem with my stored procedure. This stored procedure subtracts the values from two tables and updates a third table with the result.... er well, that's the way I want it to work.

All three tables have datatype numeric (18,1). The value in tempToday is 888932.1 and the value in tempYesterday is 864408.4

When I execute the code I get the error "Arithmetic overflow error converting varchar to data type numeric."

The SP works if I change the string @UpdateSQL to

@UpdateSQL = 'UPDATE [Reporting].[dbo].[DailyTotals] SET [1010Gas] = 24523.7 WHERE [DateAndTime] = ''' + CONVERT(CHAR(10), @TenYesterday, 120) + ''''

Here's the section of the SP that I'm working on.

Thanks for the help,
Lou

Select @GasUsed =
Case when SUM(CASE WHEN tempToday.TagName = 'SERVER01.FI1005T_TOT.F_CV' THEN tempToday.Value-COALESCE(tempYesterday.Value,0) ELSE 0 END) < 0 then
			SUM(CASE WHEN tempToday.TagName = 'SERVER01.FI1005T_TOT.F_CV' THEN tempToday.Value-COALESCE(tempYesterday.Value,0)ELSE 0 END) +1000000
		else
			SUM(CASE WHEN tempToday.TagName = 'SERVER01.FI1005T_TOT.F_CV' THEN tempToday.Value-COALESCE(tempYesterday.Value,0)ELSE 0 END) 
		end
FROM tempYesterday
LEFT JOIN tempToday ON tempyesterday.TagName = tempToday.TagName 

declare @UpdateSQL varchar(4000)
select @UpdateSQL = 'UPDATE [Reporting].[dbo].[DailyTotals] SET [1010Gas] = ''' + @GasUsed + ''' WHERE [DateAndTime] = ''' + CONVERT(CHAR(10), @TenYesterday, 120) + ''''

exec (@UpdateSQL)

 

[AlexCuse]

Have you tried not putting quotes around @GasUsed (so it is treated as numeric).

If that doesn't work you may need a convert.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ideasworking]

I think I am putting quotes around @GasUsed. Can you provide an example of what you mean?

Thanks,
Lou

 

[ideasworking]

I also tried the following which result in the same result.

select @UpdateSQL = 'UPDATE [Reporting].[dbo].[DailyTotals] SET [1010Gas] = ''' + convert(numeric(18,1),@GasUsed) + ''' WHERE [DateAndTime] = ''' + CONVERT(CHAR(10), @TenYesterday, 120) + ''''

 

[AlexCuse]

I meant without quotes, but I don't think that's your problem.

You need to replace @GasUsed in @UpdateSQL with this:

convert(varchar(50), @GasUsed)

The problem is not in your update, but in including the numeric value in your varchar string. It's trying to do math, rather than build a string.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLBill]

This might help you with the problem. + can mean either CONCATENATE or ADD depending on the data type of the value next to it.

1 + 2 makes the + mean add
'Hello' + 'Goodbye' makes the + mean concatenate

1 + 'Hello' and SQL doesn't know what the + should mean, so it makes it ADD since a number is involved. For this to work (become concatenate), you must convert the 1 to a string value (Char/Varchar/NChar/NVarchar).

-SQLBill

Posting advice: FAQ481-4875

 

[ideasworking]

Alex,

Thank you so much... I was about to erase this problem from my brain with a bottle of rum. Now I'm going to celebrate finishing this SP with that same bottle of rum. Either way the bottle never had a chance, ha ha ha.

It never occured to me that I had to convert to to anything other than numeric. I really appreciate your help.

Cheers!
Lou

 

[AlexCuse]

Glad you got it to work.

And I hope it's not too big of a bottle ;-)

Alex

Ignorance of certain subjects is a great part of wisdom