SQL Server 2000 gives us the ability to write our own (user-defined) functions. I am sure this is not news to most of you, but up until today I have not taken the time to actually write one, but after spending too much time on debugging a divide by zero error in one of my views yesterday (due to all the funkie convert logic = hard to read on a single monitor) I decided to write one that would simplify my currency formatting.

Currently for a view that a report of mine uses I have a statement that is something like this:

SELECT LEFT(CONVERT(VARCHAR(CONVERT(MONEY,345.54))),LEN(CONVERT(VARCHAR(CONVERT(MONEY,345.54))))-3)

Needless to say, there are several columns that I have to do this funkie conversion stuff just to spit the numbers out the way the user wants to see it on the report...so today I decided to "simply and economize" - (think Po Bronson's movie The First $20 Million Is Always the Hardest)

This is the code for the function:

CREATE FUNCTION dbo.FormatCurrency
(
 @number DECIMAL (15,6),
 @dec INT,
 @dollar BIT
)
RETURNS varchar(20) AS
BEGIN
 DECLARE @str VARCHAR(20)
  DECLARE @cur VARCHAR(20)
 -- use default covert from decimal -> money -> varchar
 SET @str = CONVERT(VARCHAR,CONVERT(MONEY,@number),1)
 -- determine whether to prepend a $ sign
 IF (@dollar = 1)
  SET @str = '$' + @str
 -- determine trim off digits as asked for
 IF (@dec = 0) -- take off period and 2 digits
  SET @cur = LEFT(@str, LEN(@str)-3)
 ELSE IF (@dec = 1) -- trim off
  SET @cur = LEFT(@str, LEN(@str)-1)
 ELSE IF (@dec = 2)
  SET @cur = @str
 -- return currency formatted varchar
 RETURN @cur
END

Now that I have an inline function I can use, the SQL looks a little easier:

SELECT dbo.formatcurrency(345.54, 2, 1) AS Amount
Amount
--------------------
$345.54

Since this is my first SQL User defined function, I am sure there are ways to improve this function, any feed back on how to do so is welcomed!