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