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 =

Solution 1:

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 beforeROUND() 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().

Solution 2:

Round will be calculated after its contents is evaluated.

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