any way i have the function is working perfectly.. and i have modified it.. because the above one will give error if you passed integer number.
here is the new one
CREATE FUNCTION [dbo].[UDF_FORMAT_NUMBER]
(
@NUMBER FLOAT,
@DECIMAL_PLACES INT
)
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @NUMBER_INT_PART BIGINT
DECLARE @NUMBER_DECIMAL_PART FLOAT
DECLARE @DECIMAL_NUMBER_STR VARCHAR(300)
DECLARE @NUMBER_DECIMAL_PART_STR VARCHAR(300)
SET @NUMBER_INT_PART = CONVERT(BIGINT,@NUMBER)
SET @NUMBER_DECIMAL_PART = @NUMBER - @NUMBER_INT_PART
if @NUMBER_DECIMAL_PART>0
begin
SET @NUMBER_DECIMAL_PART_STR = RIGHT(CONVERT(VARCHAR,@NUMBER_DECIMAL_PART),LEN(CONVERT(VARCHAR,@NUMBER_DECIMAL_PART))-2)
SET @DECIMAL_NUMBER_STR =left(@NUMBER_DECIMAL_PART_STR + REPLICATE('0', @DECIMAL_PLACES),@DECIMAL_PLACES)
end
else
SET @DECIMAL_NUMBER_STR = REPLICATE('0', @DECIMAL_PLACES)
RETURN CONVERT(VARCHAR,@NUMBER_INT_PART) + '.' + @DECIMAL_NUMBER_STR
END