Labels

Monday, January 28, 2013

SQL UDF to get Currency in Comma seperated


CREATE FUNCTION [dbo].[fn_CurrencyInComma]
(
       @Value DECIMAL(18, 2)
)
RETURNS VARCHAR(50)
AS
BEGIN
       DECLARE       @s VARCHAR(50),
              @ptr SMALLINT
       --FOR INDIA SET 2, AND FOR UK SET 3
       DECLARE @IndiaOrUK SMALLINT = 2

       SELECT @s = CAST(@Value AS VARCHAR(50)),
              @ptr = DATALENGTH(@s) - 5

       WHILE @ptr >= 2
              SELECT @s = STUFF(@s, @ptr, 0, ','),
                     @ptr = @ptr - @IndiaOrUK

       RETURN @s
END

No comments:

Post a Comment