April 02, 2010

Automatically Round off numbers to the nearest digit

I was solving a requirement where data entered into a table contained decimals. However the user had a requirement of automatically rounding off the decimals to the nearest digit before inserting into the table. The SQL statements could not be changed to make use of the Round() function. So one of the ways of solving this was to use the numeric datatype in the table definition as shown below:

DECLARE @TmpTable TABLE
(
id int,
amount numeric(7,0)
)

INSERT INTO @TmpTable VALUES (1, 5.556233)
INSERT INTO @TmpTable VALUES (1, 2.123953)
INSERT INTO @TmpTable VALUES (1, 3.753433)

SELECT * FROM @TmpTable

OUTPUT

image

As you can see, data stored into the table automatically gets rounded off.


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

2 comments:

Dave Levy said...

Seems kind of risky to rely on implied functionality instead of just using the Round (http://msdn.microsoft.com/en-us/library/ms175003.aspx) statement.

Does using the temp table buy you anything else?

Suprotim Agarwal said...

Dave: Nice point. The client did not want to change the sql statement. They were ok making changes in the schema instead, hence the solution. I have added that point to the post.