MS SQL Server - Question on SQL

Author

Question on SQL

Kenneth

2006-04-04, 3:23 am

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_Co
de,
@In_Val Amount
)RETURNS Amount AS
BEGIN

DECLARE
@Out_Val Amount,
@Ex_Rate Ex_Rate,
@Cal_Method Cal_Meth
od

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

John Bell

2006-04-04, 7:25 am

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_Curr Currency
_Code,
> @AC_Curr Currency_C
ode,
> @In_Val Amount
> )RETURNS Amount AS
> BEGIN
>
> DECLARE
> @Out_Val Amount,
> @Ex_Rate Ex_Rate,
> @Cal_Method Cal_Met
hod
>
> 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
>
>

Hugo Kornelis

2006-04-06, 8:23 pm

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

Kenneth

2006-04-07, 3:24 am

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
>

Sponsored Links