Monday, February 7, 2011

Format value using UDF based on format type

I encountered a situation where I had to return formatted value to the front end site based on the format type passed. Format type can be dollar ($), number (#) or percentage (%). I am not sure if this is good way to format value based on type passed, but worked for me perfectly –

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DW].[fnGetFormattedValue]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [DW].[fnGetFormattedValue]
GO

CREATE FUNCTION
[DW].[fnGetFormattedValue]
(
@Value FLOAT
,
@FormatType Varchar(50
)
)

RETURNS VARCHAR(50
)
AS

BEGIN

DECLARE
@ReturnValue VARCHAR(100
)
DECLARE @TempValue DECIMAL(10, 2
)

SET @TempValue = CONVERT (DECIMAL(10, 2), ROUND(@Value, 2
))
SET @ReturnValue
=

CASE @FormatType WHEN '$' THEN '$' + CAST (@TempValue AS VARCHAR
)
WHEN '%' THEN CAST (@TempValue AS VARCHAR) +
'%'
WHEN '#' THEN CAST (@TempValue AS VARCHAR
)
ELSE CAST (@TempValue AS VARCHAR
)
END

RETURN
@ReturnValue

END
GO

No comments:

Post a Comment