Arithmetic overflow error converting int to data type numeric

Question

How can I fix this error in T-SQL? The following line executes successfully:

SET @dPERCENT_QC_COMPLETED = CASE WHEN @NUM_QC_RECEIVED = 0 
                               THEN 0 
                               ELSE @NUM_QC_COMPLETED / @NUM_QC_RECEIVED 
                             END

But this line fails with error code below:

SET @dPERCENT_QC_COMPLETED = CASE WHEN @NUM_QC_RECEIVED = 0
                               THEN 0
                               ELSE (100 * @NUM_QC_COMPLETED / @NUM_QC_RECEIVED)
                             END

Msg 8115, Level 16, State 8, Line 73 Arithmetic overflow error converting int to data type numeric.

@dPERCENT_QC_COMPLETED is a decimal(2). The other variables are integers. Problem I think is that currently these other integer values = 1, so this expression evaluates to 1.

Answer 1

decimal(2) is never going to able to cope with 100 percent complete. Why are you using that datatype?