From the long list of missing functionalities in the world of t-SQL comes the long-winded topic of numeric formatting. Seriously, how can we come so far and still be missing such basic functionality? Whatever the case, this handy little function will get you to where you need to be.

• Example usage: SELECT dbo.FormatDecimal(87242.5345, 2)
• Example Output: 87,242.53

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