From the long list of missing functionality in the world of t-SQL comes the long-winded topic of currency 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.FormatCurrency(86414.5556, 2, '$')
• Example Output: $86,414.56

ALTER FUNCTION FormatCurrency
(
	@Value	Decimal(38, 8),
	@DeciPlaces		INT = 0,
	@Prefix			VarChar(8) = '$'
)
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 @Value<0 THEN 1 ELSE 0 END

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

	SET @ReturnValue = Convert(VarChar(38), IsNull(@Value, 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)

	SET @ReturnValue = @Prefix + @ReturnValue

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

	RETURN @ReturnValue
END