Converting int to decimal in procedure

Question

Hey guys I wrote a procedure to convert an integer to decimal, but whenever I execute the procedure I get this error:

Msg 8115, Level 16, State 8, Procedure gradesInsert, Line 9
Arithmetic overflow error converting numeric to data type numeric.

Msg 8115, Level 16, State 8, Procedure gradesInsert, Line 10
Arithmetic overflow error converting numeric to data type numeric.

This is the table that I created.

CREATE TABLE French 
(
        StudentId int Primary key,
        StudentName varchar(75),
        Class varchar (15),
        SubjectName varchar(25) DEFAULT ('Mathematics'),
        FirstPeriod int,
        SecondPeriod int,
        ThirdPeriod int,
        FirstSemesterExam int,
        FirstSemesterAvg decimal(2,2),
        FourthPeriod int,
        FifthPeriod int,
        SixPeriod int,
        SecondSemesterExam int,
        SecondSemesterAvg decimal(2,2)
)

This is the procedure and how it is called.

create proc gradesInsert
    @StuId int, @p1 int, @p2 int, @p3 int, @firstExam int, 
    @p4 int, @p5 int,   @p6 int, @SecondExam int
As
    declare @add1 int = @p1 + @p2 + @p3 + @firstExam
    declare @add2 int = @p4 + @p2 + @p6 + @SecondExam

    declare @firstAvg decimal(2,2) = cast(@add1 / 4.0 as decimal(2,2))
    declare @SecondAvg decimal(2,2) = cast(@add2 / 4.0 as decimal(2,2))
begin
    update dbo.French
    Set FirstPeriod = @p1,
        SecondPeriod = @p2,
        ThirdPeriod = @p3,
        FourthPeriod =@p4,
        FirstSemesterExam = @firstExam,
        FirstSemesterAvg = @firstAvg,
        FifthPeriod = @p5,
        SixPeriod = @p6,
        SecondSemesterExam = @SecondExam,
        SecondSemesterAvg = @SecondAvg
    where 
        dbo.French.StudentId = @StuId
end



Exec gradesInsert 1, 90, 98, 77, 69, 70, 72, 99, 100

I want the student average to have two numbers to the left of the point and scale two places to the right of the decimal point like ex (99.43).

I don't know what I'm missing here guys, but I will appreciate you honest opinions and feedbacks.

Thanks Guys!!

marc_s marc_s · Accepted Answer · 2016-08-13T08:03:20

The definition of a DECIMAL column in SQL Server is defined as:

DECIMAL(precision, scale)

where precision stands for the total number of digits, and scale for the number of digits after the decimal point.

So decimal(2,2) means: a total of 2 digits, thereof 2 after the decimal point.

With this declaration, you can basically only store values from .00 to .99 in that column. Is that really what you want/need??

Read all the details about decimal and numeric types in SQL Server on MSDN .....

Converting int to decimal in procedure

2 Answers