User-1133898271 posted
create this function and then execute it
Create FUNCTION [dbo].[udf_NumberToCurrency]
(
@InNumericValue NUMERIC(38,2)
,@InFormatType VARCHAR(10)
)
RETURNS VARCHAR(60)
AS
/*
select dbo.udf_NumberToCurrency (123411.112,'ind')
*/
BEGIN
DECLARE @RetVal VARCHAR(60)
,@StrRight VARCHAR(5)
,@StrFinal VARCHAR(60)
,@StrLength INT
SET @RetVal = ''
SET @RetVal= @InNumericValue
IF(@InFormatType = 'US')
BEGIN
SET @StrFinal= CONVERT(VARCHAR(60), CONVERT(MONEY, @RetVal) , 1)
END
ELSE
IF(@InFormatType = 'IND')
BEGIN
SET @StrLength = LEN(@RetVal)
IF(@StrLength > 6)
BEGIN
SET @StrFinal = RIGHT(@RetVal,6)
SET @RetVal = SUBSTRING(@RetVal,-5,@StrLength)
SET @StrLength = LEN(@RetVal)
IF (LEN(@RetVal) > 0 AND LEN(@RetVal) < 3)
BEGIN
SET @StrFinal = @RetVal + ',' + @StrFinal
END
WHILE LEN(@RetVal) > 2
BEGIN
SET @StrRight=RIGHT(@RetVal,2)
SET @StrFinal = @StrRight + ',' + @StrFinal
SET @RetVal = SUBSTRING(@RetVal,-1,@StrLength)
SET @StrLength = LEN(@RetVal)
IF(LEN(@RetVal) > 2)
CONTINUE
ELSE
SET @StrFinal = @RetVal + ',' + @StrFinal
BREAK
END
END
ELSE
BEGIN
SET @StrFinal = @RetVal
END
END
SELECT @StrFinal = ISNULL(@StrFinal,00)
RETURN @StrFinal
END
