Currency Conversion problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Currency Conversion problem

Author

Topic

ganatra.neha
Starting Member

30 Posts

Posted - 2007-10-29 : 16:21:56

My task is to write a stored procedure for the following

I have 2 tables name Sales.Currency and Sales.Currency Rate'

Sales.Currency has t2 colums name of currency and the currency code

Sales.CurrencyRate has colums name Currency Code, From Currency Code,To Currency Code, Average Rate, Currency exchange Rate.
all the values in From CurrencyCode Column is U.S.dollar

Follwing input parameters , NAme of the currency,
Amount to be converted, Name of the currency in which it should be converted, Currency exchange rate date

Output parameter
Converted amount

Following is my code
Use AdventureWorks
Go
Alter Procedure Currency
@nameofcurr nvarchar(50),
@amountgiven decimal(38,0),
@convertcurrency nvarchar(50),
@Date datetime,
@convertedamt decimal(38,0) output

As
Declare @a nvarchar(10)
Declare @b nvarchar(10)
Declare @c decimal (38,0)

Select @a= SC.CurrencyCode
From Sales.Currency as SC
Where @nameofcurr = SC.[Name]

Select @b = Sc.CurrencyCode From Sales.Currency as SC
Where @convertcurrency = SC.[Name]

Select @Date = SCR.CurrencyRateDate,
@c = SCR.AverageRate From Sales.CurrencyRate as SCR
Inner Join
Sales.Currency as SC
On SC.Currencycode = SCR. ToCurrencyCode
Where @b = SCR.ToCurrencyCode

Set @convertedamt = @amountgiven * @c

DECLARE @convertedamt decimal (38,0)
Execute Currency USDollar, $1,CanadianDollar,6 July 2001 12 a.m.,
@convertedamt output
Print @convertedamt

The error i get is

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'July'

Can you let me know where i am going wrong

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-29 : 16:31:24

Umm. Wow.

Your execute statement isn't written in a way that can be executed, what are you intending the "Execute CUrrency USDollar, $1, CanadianDollar,6 July 2001 12 a.m" to do?

tm
Posting Yak Master

160 Posts

Posted - 2007-10-29 : 16:43:54

You should refer to Books On Line or research into executing stored procedures.

Basic error with Execute .. Use single quotes with strings and dates.

ganatra.neha
Starting Member

30 Posts

Posted - 2007-10-29 : 17:31:58

I changed my execution statement
DECLARE @convertedamt decimal (38,0)
Execute Currency 'USDollar', 2,'CanadianDollar', '2001.02.07 12AM',
@convertedamt output
Print @convertedamt

THe query gets executed but does not show any value

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 03:00:55

Maybe it doesn't find any rows?

Put some debug code in:


Select @a= SC.CurrencyCode
From Sales.Currency as SC
Where @nameofcurr = SC.[Name]

SELECT '@a Query', [@nameofcurr] = @nameofcurr, [@a] = @a, [Rowcount] = @@ROWCOUNT

and so on so you can see what is going on. Once you've debugged it you can remove, or comment-out, the debug statements

Kristen

ganatra.neha
Starting Member

30 Posts

Posted - 2007-10-30 : 14:17:48

Thank you for guidance but my code is stillnot executing
The problem is that @c does not pick up the value from the table

I tried debug the code and know that it is not picking up the value but not able to ascertain why

Please can you let me know where i am wrong in my code

The code is as follows
Use AdventureWorks
Go

Alter Procedure Currency
@nameofcurr nvarchar(50),
@amountgiven decimal(38,4),
@convertcurrency nvarchar(50),
@Date datetime,
@convertedamt decimal(38,4) output

As
Declare @a nvarchar(10)
Declare @b nvarchar(10)
Declare @c decimal (38,4)

Select @a= SC.CurrencyCode From Sales.Currency as SC
Where @nameofcurr = SC.[Name]

Select @b = SC.CurrencyCode From Sales.Currency as SC
Where @convertcurrency = SC.[Name]

Select @c=SCR.EndofDayRate From Sales.CurrencyRate as SCR
Where
SCR.CurrencyRateDate = @Date
--and
--SCR.ToCurrencyCode = @b

SELECT '@c Query', [@c] = @c, [@c] = @c,
[Rowcount] = @@ROWCOUNT

Set @convertedamt = @amountgiven * @c

When executed it shows that variable c has null value

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 14:22:41

Select @c=SCR.EndofDayRate From Sales.CurrencyRate as SCR
Where SCR.CurrencyRateDate = @Date

You haven't set @Date to a value, so it won't match anything ...

ganatra.neha
Starting Member

30 Posts

Posted - 2007-10-30 : 14:25:47

@date is an input parameter
so when i a m executing the procedure with follwing code

DECLARE @convertedamt decimal (38,4)
Execute Currency 'US Dollar', 1,'Yen', '2001.01.07 12:00:00AM',
@convertedamt output
Print @convertedamt

It does not return any value

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 14:34:45

"@date is an input parameter"

Sorry, I misread your original post

What is the output from

SELECT '@c Query', [@c] = @c, [@Date] = @Date, [@b] = @b, [Rowcount] = @@ROWCOUNT

(inserted in place of your current debug statement) please?

Kristen

ganatra.neha
Starting Member

30 Posts

Posted - 2007-10-30 : 14:48:14

follwing is the output
@c
--------
@c Query NULL

@Date
---------
2001-01-07 00:00:00.000

@b
----------
JPY

Rowcount
------------
0

(1 row(s) affected)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-30 : 14:52:09

Does the date exist in your CurrencyRate table.

If it tries to set the CUrrencyRate based on the EndofDayRate in that table, where the CurrencyRateDate = @Date and the CurrencyRateDate has no match, the value will be null. Either that or the EndofDayRate in the table for that date is null.

sorry to jump in here.

ganatra.neha
Starting Member

30 Posts

Posted - 2007-10-30 : 15:15:15

follwing is the output
@c
--------
@c Query NULL

@Date
---------
2001-01-07 00:00:00.000

@b
----------
JPY

Rowcount
------------
0

(1 row(s) affected)

ganatra.neha
Starting Member

30 Posts

Posted - 2007-10-30 : 15:17:57

Please do not say sorry. Rather I would like to thank you for helping me

It does have that date i will just send the exceprt from the table

1 7/1/2001 12:00:00 AM USD ARS 1.0000 1.0002
2 7/1/2001 12:00:00 AM USD AUD 1.5491 1.5500
3 7/1/2001 12:00:00 AM USD BRL 1.9379 1.9419
4 7/1/2001 12:00:00 AM USD CAD 1.4641 1.4683
5 7/1/2001 12:00:00 AM USD CNY 8.2781 8.2784
6 7/1/2001 12:00:00 AM USD DEM 1.8967 1.8976
7 7/1/2001 12:00:00 AM USD EUR 0.9697 0.9703
8 7/1/2001 12:00:00 AM USD FRF 6.3611 6.3613
9 7/1/2001 12:00:00 AM USD GBP 0.6183 0.6183
10 7/1/2001 12:00:00 AM USD JPY 104.9100 104.9580
11 7/1/2001 12:00:00 AM USD MXN 9.3740 9.3840
12 7/1/2001 12:00:00 AM USD SAR 3.7507 3.7584
13 7/1/2001 12:00:00 AM USD VEB 634.5099 634.6000
14 7/2/2001 12:00:00 AM USD ARS 1.0000 0.9991
15 7/2/2001 12:00:00 AM USD AUD 1.5559 1.5558
16 7/2/2001 12:00:00 AM USD BRL 1.9339 1.9330
17 7/2/2001 12:00:00 AM USD CAD 1.4661 1.4637
18 7/2/2001 12:00:00 AM USD CNY 8.2781 8.2774
19 7/2/2001 12:00:00 AM USD DEM 1.8924 1.8922

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-30 : 15:20:10

@C is null. That means your query to populate that variable is not returning anything. Check the source data to make sure either the date actually exists, the end of day currency rate exists, or that your JOIN actually joins the data together properly.

ganatra.neha
Starting Member

30 Posts

Posted - 2007-10-30 : 15:20:48

First column is Currency Id, CurrencyRateDate,ToCurrencyCode,AverageRate,EndOfDayRate

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-30 : 16:20:28

Assuming the SALES.Currency is a table, and SALES.CurrencyRate are separate tables, your output for:

@Date
---------
2001-01-07 00:00:00.000

does not match your intended match of
2001-01-07 12:00:00 AM

Since they are different, your @c variable will populate with null.

I would do :
Select @c=SCR.EndofDayRate
From Sales.CurrencyRate as SCR
Where Cast(SCR.CurrencyRateDate as smalldatetime) = Cast(@Date as smalldatetime)

As predicted, the dates don't match exactly, so the above part of your code as you have will not produce a result for @c

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 16:58:15

Probably worth checking if there are any "near" values:


Select TOP 100 *
From Sales.CurrencyRate as SCR
Where SCR.CurrencyRateDate >= '20010107'
      AND SCR.CurrencyRateDate < '20010108'

If you don't get any rows then there are no entries in your Sales.CurrencyRate table with that date.

Are you sure that your [CurrencyRate] table should be OWNED BY [Sales] ??

Kristen

ganatra.neha
Starting Member

30 Posts

Posted - 2007-10-30 : 17:38:34

Thank you it works

it was date and time format problem. I did as you guided me.

Thank you once again

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 18:14:48

If SCR.CurrencyRateDate contains Time, as well as Date, then you can select everything on a given date using:


Select @c=SCR.EndofDayRate From Sales.CurrencyRate as SCR
Where SCR.CurrencyRateDate >= DATEADD(Day, DATEDIFF(Day, 0, @Date), 0)
      AND SCR.CurrencyRateDate < DATEADD(Day, DATEDIFF(Day, 0, @Date)+1, 0)

to demonstrate this:


SELECT DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0) AS [Start],
       DATEADD(Day, DATEDIFF(Day, 0, GetDate())+1, 0) AS [End]

Start will be midnight last night, and End will be midnight tonight.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources