0

I have a table called AssignmentMarks that stores Student's assignment marks for different subjects. This table has a column called Marks varchar(10) which stores the marks. The marks can be like 1, 2, and also it can be +, - or -+ which represents each sign a specific marks.

Note: + is 1 mark, - is -1 and -+ is 0.5

While getting a student marks from the table I am facing the Arithmetic Overflow error which I don't know what is the cause of this error.

The query is as follow:

SELECT SUM(
   CAST(
      CASE 
       WHEN a.Marks IS NULL OR a.Marks = '' THEN 0
       WHEN a.Marks = '+' THEN 1
       WHEN a.Marks = '-' THEN -1
       WHEN a.Marks = '-+' THEN 0.5
       ELSE a.Marks
      END AS DECIMAL(5,2)
   )
)
FROM AssignmentMarks AS a WHERE a.StudentID=10
AND a.SubjectID=1

After Executing the above query I get the following error.

Msg 8115, Level 16, State 8, Line 7

Arithmetic overflow error converting varchar to data type numeric.

Any idea what is the main cause of this error?

Sample Data: SQL Fiddle

  • What does this return? select * from AssignmentMarks a where try_cast(a.Marks as decimal(5,2)) is null and a.Marks not in ('+','-+','-') and a.Marks is not null and a.Marks != '' – scsimon Aug 4 at 7:16
  • @scsimon returns nothing – Asrar Ahmad Ehsan Aug 4 at 7:21
4

From error message,

Arithmetic overflow error converting varchar to data type numeric.

My first place where I could change is :

 ELSE a.Marks

and change it to

ELSE try_convert(int, a.Marks) 

or convert to decimal , if you want

Later edit:
In case expression you have a few THEN with different data types: int, numeric, varchar. Due to data type precedence, when combines different data types, the lower data type is converted to the type with higher precedence See here information about it.

you can see this , by this example:

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',  
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',  
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select 0 as col) as a  

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',  
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',  
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select 1 as col) as a

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',  
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',  
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select -1 as col ) as a

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',  
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',  
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select 0.5 as col) as a   

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',  
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',  
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select '-+' as col) as a 



Base Type   Precision   Scale
int         10          0
Base Type   Precision   Scale
int         10          0
Base Type   Precision   Scale
int         10           0
Base Type   Precision   Scale
numeric     1           1
Base Type   Precision   Scale
varchar     0           0
  • Thanks it worked, One thing that I didn't understand is that if I change the 0.5 to 10.5 or higher then it doesn't give me the error. Why? – Asrar Ahmad Ehsan Aug 4 at 8:22
  • in case expression WHEN a.Marks = '-+' THEN 0.5 changing to WHEN a.Marks = '-+' THEN 10.5 – Asrar Ahmad Ehsan Aug 4 at 8:24
  • so why casting 0.5 decimal gives error and casting 10.5 to decimal does not – Asrar Ahmad Ehsan Aug 4 at 8:36
  • the error it wasn't from casting 0.5 to decimal. It is related to Data type precedence , see here docs.microsoft.com/en-us/sql/t-sql/data-types/… – Sabin Bio Aug 4 at 8:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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