July 10, 2008 at 4:38 pm
I need help with this!!
What i am trying to do is this -
I am getting value from a table.
I want to add currency symbol based on my regional settings to this value. I want to do this in my stored procedure before the value reaches my front end.
Please help.
Thanks
July 11, 2008 at 7:06 am
You'll have to store the symbol and it's associated currency in a table in order to select on it. Otherwise you could create an enormous CASE statement, but the first solution will be easier to implement and maintain.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 15, 2008 at 11:46 pm
Can't we take it from registry settings using CLR ?
July 16, 2008 at 4:34 am
Since all the work is being done on the server, how many different settings are likely to come out?
What they want to do, if I'm reading this correctly, is get the regional settings from the individual, pass them in along with the query and then pass the data back out. A simple lookup table with a join sure seems like an easy way to accomplish this. I'm pretty sure that data is available from ISO, so it's not like you'll have to key it in or anything.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 17, 2008 at 4:54 am
I'm not against whats been said by Grant but....
Why is the currency type symbol not already stored as a lookup table within the Database?
e.g. table called currencies
columns of id INT, symbol NVARCHAR(3), text NVARCHAR(50) - speculative sizes
e.g.
data
id symbol text
1 $ US Dollars
2 £ Pounds Sterling
3 $Cn Canadian Dollars (dunno if you do Canadian like this).
...
n X whatever x is
Every currency record would have a 'currency id' associated with it via FK Constraint.
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
July 17, 2008 at 5:59 am
That's not against what I said at all. That's what I've been trying to suggest. Thanks for stating it in a different way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 17, 2008 at 10:37 am
Agreed! But this application has been in existence for years and was used in US until this year where we have the application in other countries. Yes, its a flaw in the design that they didnt think ahead but i guess this is what i have to live with. Adding a new currency look up table might not be possible but i can store the currency symbol in another table and look it up.
Before i settled for that solution, i wanted to know if there was any way to get it based on regional settings.
July 18, 2008 at 1:47 am
Sorry Grant; Yes I see we are in agreement. 🙂
charanyabs: are you saying you are allowed to write/alter stored procs, but not able to alter tables or add new ones to the db?
Ok correct me if I'm wrong but I gather your issues are
1.The application has been deployed in countries other than the USA.
2.It does currency in US$.
3.The $ symbol is prepended in the DB before data is returned to the application.
4.You want to override the $ symbol using a stored proc.
5.The local application will only be used by users in the local i.e. if its deployed in France only french users will use that instance of the app and hence you need Euro's.
My suggestion is a single row table containing one column for currency symbol and you need to set this to your local symbol when deploying the DB.
Add a variable to your stored proc and SELECT into it the symbol from the table and then concatenate the variable as required.
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
July 18, 2008 at 10:17 am
That's precise!!
And that's exactly what i ended up doing. Added a row in a table that will hold the currency symbol. The value depends on which country uses the application.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply