CODEWITHSUNDEEP
sql-serverdivisionrounding
superM
superM

Reputation: 8695

SQL Server round after division

In a stored procedure I have an expression like

select @some_val_in_percents = (@total_val / 100) * @some_val

If I use the Round function like this:

select @some_val_in_percents = Round((@total_val / 100) * @some_val, 0)

will the result be rounded when the whole expression is calculated or will (@total_val / 100) be rounded and than multiplied by @some_val?

Upvotes: 5

Views: 26358

Answers (2)

Andriy M
Andriy M

Reputation: 77747

You seem to be calculating the percent value wrongly. Here's what I would expect it to be like:

@some_val * 100 / @total_val

As for the ROUND() function, it will act on the final result of the expression, not on the partial result. So, first the expression is evaluated completely, then ROUND() is applied to the result.

Note also (in case you haven't already known it) that if both operands of the division operator are integers, SQL Server will perform an integer division, i.e. the result of the division would be rounded down to the nearest integer even before ROUND() is applied. If you want to avoid that, make sure that at least one of the operands is not integer, e.g. like this:

ROUND(@some_val * 100.0 / @total_val, 2)

Note also the second argument (precision), which is required in Transact-SQL ROUND().

Upvotes: 12

Curtis
Curtis

Reputation: 103428

Round will be calculated after its contents is evaluated.

Therefore (@total_val / 100) * @some_val will be rounded.

Upvotes: 2

Related Questions