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
>