ingenious code ::

Recently, I encountered a situation in which I needed to retrieve formated currency values directly from my database. As rule of thumb, in 95% of all cases, the data should be retrieved as a raw number (with no formatting) and should be formated in the client application, but in this situation I needed a formated string.

In most cases a query like this:

use AdventureWorks
go
select sum(TotalDue) from Sales.SalesOrderHeader

will return: 140707584.8246

In order to resolve this issue I create a small user defined function that formats the values and return it a well formated currency value.

alter function udf_CurrencyFormat  (@number money)
returns varchar(100) as
begin
return '$' + convert(varchar,cast(@number  as money),1)
end 

After deploying this function a query like this:

select dbo.udf_CurrencyFormat(sum(TotalDue))
from Sales.SalesOrderHeader 

Will Return: $140,707,584.82