
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE FUNCTION [dbo].[f_format_number]
(
	@value ais_decimal2,
	@precision tinyint,
	@tsep nvarchar(2),
	@dsep nvarchar(2)
)
RETURNS nvarchar(32)
AS
BEGIN

	if (@value is null) return ''
	
	--set @value = ROUND(@value, @precision)
	set @precision = isnull(@precision,0)
	
	declare @money_str nvarchar(32) = convert(nvarchar(32), convert(money, @value), 1)
	
	declare @ppos tinyint = charindex('.', @money_str)
	
	set @money_str = LEFT(@money_str, @ppos - 1 + case when @precision = 0 THEN 0 ELSE @precision + 1 end )
	
	set @money_str = REPLACE(@money_str, ',', @tsep)
	set @money_str = REPLACE(@money_str, '.', @dsep)
	
	return @money_str
	
	/*
	
	declare @bigint_value bigint = abs(convert(bigint,@value))
	declare @bigint_value_str varchar(32)= CONVERT(varchar(32), @bigint_value) 
	declare @bigint_value_str_len tinyint = LEN(@bigint_value_str)
	
	if @bigint_value_str_len > 3
	begin
		declare @i tinyint = LEN(@bigint_value_str) % 3 + 1  
		
		while ( @i <= LEN(@bigint_value_str))  
		begin
			set @bigint_value_str = STUFF(@bigint_value_str, @i, 0, @tsep)  
			set @i += 4  
		end 
	end

	return	case when SIGN(@value) < 0 then '-' else '' end +  
			@bigint_value_str +
			case when @precision = 0 then '' else @dsep + left(replace(convert(varchar, abs(abs(@value) - @bigint_value)), '0.', ''), @precision) end
			
	*/	
			
/*
select dbo.f_format_number(100.25,2,' ', ',')
select dbo.f_format_number(1000.25,2,' ', ',')
select dbo.f_format_number(1234567.25,2,' ', ',')

select dbo.f_format_number(100.25,1,' ', ',')
select dbo.f_format_number(1000.25,1,' ', ',')
select dbo.f_format_number(1234567.25,1,' ', ',')

select dbo.f_format_number(100.25,0,' ', ',')
select dbo.f_format_number(1000.25,0,' ', ',')
select dbo.f_format_number(1234567.25,0,' ', ',')
select dbo.f_format_number(1234567.75,0,' ', ',')

select dbo.f_format_number(-100.25,2,' ', ',')
select dbo.f_format_number(-1000.25,2,' ', ',')
select dbo.f_format_number(-1234567.25,2,' ', ',')

select dbo.f_format_number(-100.25,1,' ', ',')
select dbo.f_format_number(-1000.25,1,' ', ',')
select dbo.f_format_number(-1234567.25,1,' ', ',')

select dbo.f_format_number(-100.25,0,' ', ',')
select dbo.f_format_number(-1000.25,0,' ', ',')
select dbo.f_format_number(-1234567.25,0,' ', ',')
select dbo.f_format_number(-1234567.75,0,' ', ',')

*/
end


GO
