Search code examples
sql-servernumbersdecimalscale

How To Control Scale Numbers in Decimal in Sql Server 2008

Is there any function in Sql Server 2008 that controls scale numbers.For example if we have a decimal number 123.456 and in function if pass 0 it should return 123, if i pass 1 then should return 123.4 if pass 2 then return 123.45 and if pass 3 then return 123.456.If there is no inbult function then Please let me know any user define function.Thanks, Ravi

Solution 
  • CREATE FUNCTION f_test (
@a INT,
@b FLOAT
) RETURNS DECIMAL(12,6)
AS
BEGIN
  RETURN CAST(@b * POWER(10, @a) AS INT)*1.0 / POWER(10, @a)
END

SELECT dbo.f_test(2, 123.456) AS RESULT

RESULT
----------
123.450000