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_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

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 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_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_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
>