Rounding Up Prices

The problem was posted by Steve Ledbetter:

I am trying to do some slightly complicated rounding in transact SQL in a
stored proc. I need to round UP, and have implemented this for straight
rounding up, for rounding up to nearest nickel/dime/quarter/half dollar, and
rounding up to next .49/.99 using addition and CEILING and formulas..


My difficulty is that in addition to the above options I need the option to
allow the user to select two values to round to. Example, user selects
'.03' as 'low value' and '.07' as 'high value', I need to round up to the
next .x3 or .x7, so that 1.22 would become 1.23, 1.25 would become 1.27,
1.29 would become 1.33, etc. The same idea would apply to dollars
separately, so picking .03 and .07 for cents, and 5 and 8 for dollars: 3.21
would round to 5.23 ($3 would round up to $5 .21 would round up to .23 -
the neareast .03), 6.35 would round to 8.37, 9.99 would round to 15.03,
etc.

It's for a retail products management module where clients can generate
their selling prices from costs (product cost, freight, installation cost,
etc) using formulas and rounding options (like rounding up to nearest .99.
.49 , nickel, dime, etc. or to a user defined pair of numbers (which was
what spurred my questions). I'll be updating 10's of thousands of records
at a time and needed to do it in a stored proc for speed.

My solution:

You can round up dollars as follows:

DECLARE @numberToRoundUp INT
SET @numberToRoundUp = 9
------- Assuming Numbers table stores numbers from 1 to 1000
SELECT TOP 1 Number FROM Numbers
WHERE Number % 10 IN(5,8) AND Number > @numberToRoundUp
ORDER BY Number


You can round up cents in the same way.