Format Decimal Value

This is the workhorse behind my FormatNumeric function: it takes a decimal value and a number of decimal places and returns a string with thousands separators and exactly the requested precision. It handles negative values, rounds when the source has more decimals than requested, and pads with trailing zeros when it has fewer.

• Example usage: SELECT dbo.FormatDecimal(86414.5556, 2)
• Example Output: 86,414.56

CREATE FUNCTION FormatDecimal
(
	@Numeric	Decimal(38, 8),
	@DeciPlaces		INT = 0
)
RETURNS VarChar(38)
AS
BEGIN
	DECLARE @TmpValue		VarChar(38)
	DECLARE @ReturnValue	VarChar(38)
	DECLARE @IsNegative		BIT
	DECLARE @Pos			Int
	DECLARE @Before			VarChar(38)
	DECLARE @After			VarChar(38)

	SELECT @IsNegative = CASE WHEN @Numeric<0 THEN 1 ELSE 0 END

	IF @IsNegative = 1
	BEGIN
		SET @Numeric = (-1 * @Numeric)
	END

	SET @ReturnValue = Convert(VarChar(38), IsNull(@Numeric, 0))
	
	IF CharIndex ('.', @ReturnValue )>0
	BEGIN
		SET @After = SubString(@ReturnValue,  CharIndex ('.', @ReturnValue ), Len(@ReturnValue))
		SET @Before = SubString(@ReturnValue, 1,  (CharIndex ('.', @ReturnValue ) - 1))
	END ELSE BEGIN
		SET @Before = @ReturnValue
		SET @After = ''
	END

	IF Len(@After) > @DeciPlaces
	BEGIN
		SET @After = Convert(Varchar(38), Round(Convert(Float, '1.' + Right(@After, Len(@After) - 1)), @DeciPlaces))

		set @After = Right(@After, Len(@After) - 1)
	END

	IF Len(@After) = 0 AND @DeciPlaces > 0
	BEGIN
		SET @After = '.'
	END

	WHILE (Len(@After) - 1) < @DeciPlaces AND @DeciPlaces > 0
	BEGIN
		SET @After = @After + '0'
	END

	IF (Len(@Before) > 3)
	BEGIN
		SET @Pos = 3
		WHILE((@Pos > 1) AND (@Pos < Len(@Before)))
		BEGIN
			SET @Before = SubString(@Before, 1, (Len(@Before) - @Pos)) + ',' + Right(@Before, @Pos)
			SET @Pos = (@Pos + 4)
		END
	END
	SET @ReturnValue = (@Before + @After)

	IF @IsNegative = 1
	BEGIN
		SET @ReturnValue = '-' + @ReturnValue
	END

	RETURN @ReturnValue
END
GO