SQL adding decimal resulting in : Arithmetic overflow error converting numeric to data type numeric

Question

I have two numbers stored in database as decimal with precision equals to 9. My objective is to add these two numbers and update the value in database.

DECLARE @v1 DECIMAL(9, 5), @v2 DECIMAL(9, 5)
SET @v1 = 9503.34000
SET @v2 = 1357.62000
SELECT CAST(@v1 + @v2 AS DECIMAL(9, 5))

When i add this in SQL it throws overflow error however on changing it to 10,5 it results in 10860.96000 .

How can i tell SQL to add and return result based on precision i want. I want this sum to return me 9,5 NOT 10,5.

Answer 1

It can't return DECIMAL(9,5). You have 5 digits in front of the decimal place as 9,503 + 1,357 is greater than 10,000.

If you only want 9 digits in total use CAST(@v1 +@v2 as DECIMAL(10,4)). You don't seem to use the 5th decimal place anyway.