current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -1 down vote favorite

I am getting the following error :

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

The numbers are not too big for bigint. I am unable to point out what is going wrong here. Any pointers please.

Query

DECLARE SP bigint
DECLARE LP bigint

SET SP = 125000000 
SET LP = 88300000 

SELECT a = CAST((AVG(LP * 100 / CAST(SP AS DECIMAL(10, 2)))) AS DECIMAL(10,2))
share|improve this question
    
Are you sure that's your query and your output? You're missing the @ on your variables, and it should overflow converting to numeric, not int. At least that's what happens on SQL2008. –  Joe Enos Nov 26 '14 at 21:46
    
@JoeEnos Inorder not to overwhelm I put only the erroring portion of my query. –  BumbleBee Nov 26 '14 at 21:50
    
It makes a big difference - your query that you posted will overflow because of the cast to a decimal. If you're getting an overflow error while casting to an int, then what you've posted is not applicable. –  Joe Enos Nov 26 '14 at 21:53
1  
Try posting a reproduction of your problem on sqlfiddle so that we can see exactly the error, without any transcription problems. –  Jon Egerton Nov 26 '14 at 22:08
1  
DECIMAL(10,2) gives you a decimal with a total of 10 digits, 2 of which after the decimal point - so 8 digits before the decimal point. BUT your SP value (which should be @SP, really) has nine digits - this cannot be converted to DECIMAL(10,2)! You need to use more digits in your decimal type, e.g. DECIMAL(16,2) or something like that –  marc_s Nov 27 '14 at 6:24

3 Answers 3

up vote 2 down vote

I think your int error is coming from the division. You should also be able to move the 100* out of the AVG function and not CAST quite so often:

DECLARE @SP bigint
DECLARE @LP bigint

SET @SP = 125000000
SET @LP = 88300000 

select a= 100 * avg(@LP / cast(@SP as float))

also, see this regarding division of bigints

share|improve this answer
    
If you want to see every last bit of the pi, use this select a= 100 * avg(@LP / cast(@SP as float(24))) –  Kennah Nov 26 '14 at 23:17
up vote 1 down vote

What did you try? It would have been less work just to break down the problem youself than post syntax that is not even valid on SO.

This fails 

DECLARE @SP bigint
SET @SP = 125000000
select cast(@SP as decimal(10,2))
share|improve this answer
up vote 1 down vote

Try this

DECLARE @SP bigint DECLARE @LP bigint

SET @SP = 125000000 SET @LP = 88300000

SELECT a = CAST((AVG(@LP * 100 / CAST(@SP AS DECIMAL(12, 2)))) AS DECIMAL(12,2))

I agree with marc_s comment.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.