Question on SQL

Hello,
I am new to SQL Server. I am writing a stored procedure with a sql like this
:
SELECT
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
FROM TableX
WHERE
ID = '1234'
When I run the query, all the five column return same value and I am sure
the result is incorrect. Here is the function of fn_currency. It simply
convert foreign curreny amount to base currency amount. Is there anyone who
can tell me what's wrong with it?
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER FUNCTION fn_Currency
(
@.Item_CurrCurrency_Code,
@.AC_CurrCurrency_Code,
@.In_ValAmount
)RETURNS Amount AS
BEGIN
DECLARE
@.Out_ValAmount,
@.Ex_RateEx_Rate,
@.Cal_MethodCal_Method
SELECT @.Out_Val = ISNULL(@.In_Val, 0)
IF @.Item_Curr <> @.AC_Curr
BEGIN
SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency =
@.Item_Curr AND To_Currency = @.AC_Curr
SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
AND To_Currency = @.AC_Curr
SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
IF @.Cal_Method = 'M'
SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
ELSE
SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
END
Return @.Out_Val
END
Many thanks!
Kenneth
Hi
Without DDL for the UDT and tables it is hard to replicate your environment.
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to get this. You may
also want to post what version/service pack you are using.
Your procedure should return different values, have you tried it without the
SUMs?
John
"Kenneth" wrote:

> Hello,
> I am new to SQL Server. I am writing a stored procedure with a sql like this
> :
> SELECT
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> FROM TableX
> WHERE
> ID = '1234'
> When I run the query, all the five column return same value and I am sure
> the result is incorrect. Here is the function of fn_currency. It simply
> convert foreign curreny amount to base currency amount. Is there anyone who
> can tell me what's wrong with it?
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER FUNCTION fn_Currency
> (
> @.Item_CurrCurrency_Code,
> @.AC_CurrCurrency_Code,
> @.In_ValAmount
> )RETURNS Amount AS
> BEGIN
> DECLARE
> @.Out_ValAmount,
> @.Ex_RateEx_Rate,
> @.Cal_MethodCal_Method
> SELECT @.Out_Val = ISNULL(@.In_Val, 0)
> IF @.Item_Curr <> @.AC_Curr
> BEGIN
> SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency =
> @.Item_Curr AND To_Currency = @.AC_Curr
> SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
> SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
> AND To_Currency = @.AC_Curr
> SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
> IF @.Cal_Method = 'M'
> SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
> ELSE
> SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
> END
> Return @.Out_Val
> END
> Many thanks!
> Kenneth
>
|||On Tue, 4 Apr 2006 00:24:01 -0700, Kenneth wrote:

>Hello,
>I am new to SQL Server. I am writing a stored procedure with a sql like this
>:
>SELECT
>sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
>sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
>sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
>sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
>sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
>FROM TableX
>WHERE
>ID = '1234'
>When I run the query, all the five column return same value and I am sure
>the result is incorrect. Here is the function of fn_currency. It simply
>convert foreign curreny amount to base currency amount. Is there anyone who
>can tell me what's wrong with it?
Hi Kenneth,
It looks like your server has not been upgraded with any service pack
yet. What you describe looks exactly like this problem, which was fixed
in service pack 1: http://support.microsoft.com/kb/288957/EN-US/
Hugo Kornelis, SQL Server MVP
|||I am using SQL server service pack 2 currently. However, I can still simulate
the error mentioned in the knowledge base. I am now trying to upgrade to
service pack 4 and test again. Thank you very much!
"Hugo Kornelis" wrote:

> On Tue, 4 Apr 2006 00:24:01 -0700, Kenneth wrote:
>
> Hi Kenneth,
> It looks like your server has not been upgraded with any service pack
> yet. What you describe looks exactly like this problem, which was fixed
> in service pack 1: http://support.microsoft.com/kb/288957/EN-US/
> --
> Hugo Kornelis, SQL Server MVP
>

Question on SQL

Hello,
I am new to SQL Server. I am writing a stored procedure with a sql like this
:
SELECT
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
FROM TableX
WHERE
ID = '1234'
When I run the query, all the five column return same value and I am sure
the result is incorrect. Here is the function of fn_currency. It simply
convert foreign curreny amount to base currency amount. Is there anyone who
can tell me what's wrong with it'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER FUNCTION fn_Currency
(
@.Item_Curr Currency_Code,
@.AC_Curr Currency_Code,
@.In_Val Amount
)RETURNS Amount AS
BEGIN
DECLARE
@.Out_Val Amount,
@.Ex_Rate Ex_Rate,
@.Cal_Method Cal_Method
SELECT @.Out_Val = ISNULL(@.In_Val, 0)
IF @.Item_Curr <> @.AC_Curr
BEGIN
SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency =
@.Item_Curr AND To_Currency = @.AC_Curr
SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
AND To_Currency = @.AC_Curr
SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
IF @.Cal_Method = 'M'
SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
ELSE
SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
END
Return @.Out_Val
END
Many thanks!
KennethHi
Without DDL for the UDT and tables it is hard to replicate your environment.
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to get this. You may
also want to post what version/service pack you are using.
Your procedure should return different values, have you tried it without the
SUMs?
John
"Kenneth" wrote:

> Hello,
> I am new to SQL Server. I am writing a stored procedure with a sql like th
is
> :
> SELECT
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> FROM TableX
> WHERE
> ID = '1234'
> When I run the query, all the five column return same value and I am sure
> the result is incorrect. Here is the function of fn_currency. It simply
> convert foreign curreny amount to base currency amount. Is there anyone wh
o
> can tell me what's wrong with it'
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER FUNCTION fn_Currency
> (
> @.Item_Curr Currency_Code,
> @.AC_Curr Currency_Code,
> @.In_Val Amount
> )RETURNS Amount AS
> BEGIN
> DECLARE
> @.Out_Val Amount,
> @.Ex_Rate Ex_Rate,
> @.Cal_Method Cal_Method
> SELECT @.Out_Val = ISNULL(@.In_Val, 0)
> IF @.Item_Curr <> @.AC_Curr
> BEGIN
> SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency
=
> @.Item_Curr AND To_Currency = @.AC_Curr
> SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
> SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Cur
r
> AND To_Currency = @.AC_Curr
> SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
> IF @.Cal_Method = 'M'
> SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
> ELSE
> SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
> END
> Return @.Out_Val
> END
> Many thanks!
> Kenneth
>|||On Tue, 4 Apr 2006 00:24:01 -0700, Kenneth wrote:

>Hello,
>I am new to SQL Server. I am writing a stored procedure with a sql like thi
s
>:
> SELECT
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> FROM TableX
> WHERE
> ID = '1234'
>When I run the query, all the five column return same value and I am sure
>the result is incorrect. Here is the function of fn_currency. It simply
>convert foreign curreny amount to base currency amount. Is there anyone who
>can tell me what's wrong with it'
Hi Kenneth,
It looks like your server has not been upgraded with any service pack
yet. What you describe looks exactly like this problem, which was fixed
in service pack 1: http://support.microsoft.com/kb/288957/EN-US/
Hugo Kornelis, SQL Server MVP|||I am using SQL server service pack 2 currently. However, I can still simulat
e
the error mentioned in the knowledge base. I am now trying to upgrade to
service pack 4 and test again. Thank you very much!
"Hugo Kornelis" wrote:

> On Tue, 4 Apr 2006 00:24:01 -0700, Kenneth wrote:
>
> Hi Kenneth,
> It looks like your server has not been upgraded with any service pack
> yet. What you describe looks exactly like this problem, which was fixed
> in service pack 1: http://support.microsoft.com/kb/288957/EN-US/
> --
> Hugo Kornelis, SQL Server MVP
>

Question on SQL

Hello,
I am new to SQL Server. I am writing a stored procedure with a sql like this
:
SELECT
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
FROM TableX
WHERE
ID = '1234'
When I run the query, all the five column return same value and I am sure
the result is incorrect. Here is the function of fn_currency. It simply
convert foreign curreny amount to base currency amount. Is there anyone who
can tell me what's wrong with it'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER FUNCTION fn_Currency
(
@.Item_Curr Currency_Code,
@.AC_Curr Currency_Code,
@.In_Val Amount
)RETURNS Amount AS
BEGIN
DECLARE
@.Out_Val Amount,
@.Ex_Rate Ex_Rate,
@.Cal_Method Cal_Method
SELECT @.Out_Val = ISNULL(@.In_Val, 0)
IF @.Item_Curr <> @.AC_Curr
BEGIN
SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency = @.Item_Curr AND To_Currency = @.AC_Curr
SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
AND To_Currency = @.AC_Curr
SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
IF @.Cal_Method = 'M'
SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
ELSE
SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
END
Return @.Out_Val
END
Many thanks!
KennethHi
Without DDL for the UDT and tables it is hard to replicate your environment.
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to get this. You may
also want to post what version/service pack you are using.
Your procedure should return different values, have you tried it without the
SUMs?
John
"Kenneth" wrote:
> Hello,
> I am new to SQL Server. I am writing a stored procedure with a sql like this
> :
> SELECT
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> FROM TableX
> WHERE
> ID = '1234'
> When I run the query, all the five column return same value and I am sure
> the result is incorrect. Here is the function of fn_currency. It simply
> convert foreign curreny amount to base currency amount. Is there anyone who
> can tell me what's wrong with it'
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER FUNCTION fn_Currency
> (
> @.Item_Curr Currency_Code,
> @.AC_Curr Currency_Code,
> @.In_Val Amount
> )RETURNS Amount AS
> BEGIN
> DECLARE
> @.Out_Val Amount,
> @.Ex_Rate Ex_Rate,
> @.Cal_Method Cal_Method
> SELECT @.Out_Val = ISNULL(@.In_Val, 0)
> IF @.Item_Curr <> @.AC_Curr
> BEGIN
> SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency => @.Item_Curr AND To_Currency = @.AC_Curr
> SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
> SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
> AND To_Currency = @.AC_Curr
> SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
> IF @.Cal_Method = 'M'
> SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
> ELSE
> SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
> END
> Return @.Out_Val
> END
> Many thanks!
> Kenneth
>|||On Tue, 4 Apr 2006 00:24:01 -0700, Kenneth wrote:
>Hello,
>I am new to SQL Server. I am writing a stored procedure with a sql like this
>:
> SELECT
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> FROM TableX
> WHERE
> ID = '1234'
>When I run the query, all the five column return same value and I am sure
>the result is incorrect. Here is the function of fn_currency. It simply
>convert foreign curreny amount to base currency amount. Is there anyone who
>can tell me what's wrong with it'
Hi Kenneth,
It looks like your server has not been upgraded with any service pack
yet. What you describe looks exactly like this problem, which was fixed
in service pack 1: http://support.microsoft.com/kb/288957/EN-US/
--
Hugo Kornelis, SQL Server MVP|||I am using SQL server service pack 2 currently. However, I can still simulate
the error mentioned in the knowledge base. I am now trying to upgrade to
service pack 4 and test again. Thank you very much!
"Hugo Kornelis" wrote:
> On Tue, 4 Apr 2006 00:24:01 -0700, Kenneth wrote:
> >Hello,
> >
> >I am new to SQL Server. I am writing a stored procedure with a sql like this
> >:
> >
> > SELECT
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> > FROM TableX
> > WHERE
> > ID = '1234'
> >
> >When I run the query, all the five column return same value and I am sure
> >the result is incorrect. Here is the function of fn_currency. It simply
> >convert foreign curreny amount to base currency amount. Is there anyone who
> >can tell me what's wrong with it'
> Hi Kenneth,
> It looks like your server has not been upgraded with any service pack
> yet. What you describe looks exactly like this problem, which was fixed
> in service pack 1: http://support.microsoft.com/kb/288957/EN-US/
> --
> Hugo Kornelis, SQL Server MVP
>

Question of SQL

Hello,
I am new to SQL Server. I am writing a stored procedure with a sql like this
:
SELECT
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
FROM TableX
WHERE
ID = '1234'
When I run the query, all the five column return same value and I am sure
the result is incorrect. Is there anyone who can tell me what's wrong with
it'
Many thanks!
KennethKenneth wrote:
> Hello,
> I am new to SQL Server. I am writing a stored procedure with a sql like this
> :
> SELECT
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> FROM TableX
> WHERE
> ID = '1234'
> When I run the query, all the five column return same value and I am sure
> the result is incorrect. Is there anyone who can tell me what's wrong with
> it'
> Many thanks!
> Kenneth
Please post enough code to reproduce the problem and show us what is
wrong with it. Without even seeing what fn_currency does it's hard to
help you.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Here is the function of fn_currency. It simply convert foreign curreny amount
to base currency amount.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER FUNCTION fn_Currency
(
@.Item_Curr Currency_Code,
@.AC_Curr Currency_Code,
@.In_Val Amount
)RETURNS Amount AS
BEGIN
DECLARE
@.Out_Val Amount,
@.Ex_Rate Ex_Rate,
@.Cal_Method Cal_Method
SELECT @.Out_Val = ISNULL(@.In_Val, 0)
IF @.Item_Curr <> @.AC_Curr
BEGIN
SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency =@.Item_Curr AND To_Currency = @.AC_Curr
SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
AND To_Currency = @.AC_Curr
SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
IF @.Cal_Method = 'M'
SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
ELSE
SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
END
Return @.Out_Val
END
"David Portas" wrote:
> Kenneth wrote:
> > Hello,
> >
> > I am new to SQL Server. I am writing a stored procedure with a sql like this
> > :
> >
> > SELECT
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> > FROM TableX
> > WHERE
> > ID = '1234'
> >
> > When I run the query, all the five column return same value and I am sure
> > the result is incorrect. Is there anyone who can tell me what's wrong with
> > it'
> >
> > Many thanks!
> > Kenneth
> Please post enough code to reproduce the problem and show us what is
> wrong with it. Without even seeing what fn_currency does it's hard to
> help you.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||This will not solve your former problem, but IMHO this is not a good
approach.
You should use a simple joined table and avoid the choice of the method at
the execution (multiply or divide) in the function.
Have a column with a coef and always multiply. This coef can be a computed
column or a column maintained by a trigger. ;-)
This could look like this :
select t1.amount, t1.amount * er.rate as converted_amount
from table t1
inner join exrate er
on t1.currency = er.source_currency
and er.destination_currency = 'USD'
This is a *much* faster.
"Kenneth" <Kenneth@.discussions.microsoft.com> a écrit dans le message de
news: B8BD30D8-1100-45A7-8615-8EDFCB7A494E@.microsoft.com...
> Here is the function of fn_currency. It simply convert foreign curreny
> amount
> to base currency amount.
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER FUNCTION fn_Currency
> (
> @.Item_Curr Currency_Code,
> @.AC_Curr Currency_Code,
> @.In_Val Amount
> )RETURNS Amount AS
> BEGIN
> DECLARE
> @.Out_Val Amount,
> @.Ex_Rate Ex_Rate,
> @.Cal_Method Cal_Method
> SELECT @.Out_Val = ISNULL(@.In_Val, 0)
> IF @.Item_Curr <> @.AC_Curr
> BEGIN
> SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency
> => @.Item_Curr AND To_Currency = @.AC_Curr
> SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
> SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
> AND To_Currency = @.AC_Curr
> SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
> IF @.Cal_Method = 'M'
> SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
> ELSE
> SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
> END
> Return @.Out_Val
> END
> "David Portas" wrote:
>> Kenneth wrote:
>> > Hello,
>> >
>> > I am new to SQL Server. I am writing a stored procedure with a sql like
>> > this
>> > :
>> >
>> > SELECT
>> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
>> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
>> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
>> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
>> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
>> > FROM TableX
>> > WHERE
>> > ID = '1234'
>> >
>> > When I run the query, all the five column return same value and I am
>> > sure
>> > the result is incorrect. Is there anyone who can tell me what's wrong
>> > with
>> > it'
>> >
>> > Many thanks!
>> > Kenneth
>> Please post enough code to reproduce the problem and show us what is
>> wrong with it. Without even seeing what fn_currency does it's hard to
>> help you.
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>