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!