Money Formatting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Money Formatting

Author

Topic

Ste_Donkin
Starting Member

6 Posts

Posted - 2004-05-04 : 08:40:50

Please help this newbie.

I have got a stored procedure that runs 5 seperate queries and inserts the results into 1 single table.
I the use the xp_sendmail @Query to send the results of that table to the people that need to know.

Now, being in the UK I would like the money fields to be displayed with a £ sign not a $ sign on the output.

Can anyone give me any clues about how to set the formatting to UK standards, ideally at run time?

Thanks for your help in advance

gpl
Posting Yak Master

195 Posts

Posted - 2004-05-04 : 09:10:31

Convert the money amount to a string and bung a quid sign in front

select
....
'£' + Convert(Varchar, MyMoneyAmount) As MonyAmount,
.....

You may of course have to play about with the formatting a little more to enforce the 2 decimal places

Graham

Ste_Donkin
Starting Member

6 Posts

Posted - 2004-05-04 : 10:17:47

Thanks for the quick reply, I will give it a go on one field and let you know.

As I am a running 5 queries and each query has between 10 and 15 money fields in it, is there anyway to just format the fields in the results table?

I am using SQL Server 2000 with Windows XP.

Thanks once again.

gpl
Posting Yak Master

195 Posts

Posted - 2004-05-04 : 11:03:06

Results table ?
Are you collecting a series of queries in an intermediate table then selecting from that ?

If so, only the final query needs to have the formatting on it.

Graham

Ste_Donkin
Starting Member

6 Posts

Posted - 2004-05-04 : 11:27:59

Sorry I may not have made myself clear before.

I have 5 queries that run in 1 stored procedure.

Each query follows the same pattern

Set @Select = 'Select Price from Query 1'
Set @Insert = 'Insert Into Results (Price)' + @Select

Exec sp_executeSQL @Insert

Once the 5 queries have all run and inserted the results into the Results Table I then use

Exec dbo.master.xp_SendMail 'MyDistributionList'
@Query = 'Select * from Results',
@dbuse = 'Mydbase',
@attach_results = true

The results query is then emailed as an attachement which shows as
Price
-------------
$8501.0000
$650.0000

The data in the results table shows correctly but on the attachment it shows with the $ sign. It is the @Query on the xp_sendmail that reformats the money fields.

Does this make it clearer how I have got it set up?

gpl
Posting Yak Master

195 Posts

Posted - 2004-05-04 : 11:58:54

Much clearer

Can the @Query parameter of xp_SendMail be 'exec storedprocedurename'
If so, there is your opportunity to simplify the query, even better if you encapsulate the reformatting in a user-defined function

Graham

cas_o
Posting Yak Master

154 Posts

Posted - 2004-05-04 : 12:05:06

use master
go
set language british
go
Exec xp_SendMail 'MyDistributionList'
@Query = 'Select * from dbo.Mydbase.Results',
@dbuse = 'Mydbase',
@attach_results = true

;-]... Quack Waddle

Ste_Donkin
Starting Member

6 Posts

Posted - 2004-05-05 : 03:48:34

Thanks very much to both of you, your replies have fixed this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources