Help with UDF

Hi,
I've not created one of these before so if anyone can help me get this up
and running that would be great. I want to write a function to obtain the
exchange rate from a table based on three parameters - exhange rate, country
& year.
The complication I have is I want to pass in the value 1 for Euro and 2 for
Dollar so in the function I need an "if" statement to run the appropiate
select statement. So in the function it would be something like this...
if @.iExchangeRate = 1
BEGIN
select Euro from ExchangeRates where CountryID = 35 and YearID = 5
END
ELSE
BEGIN
select Dollar from ExchangeRates where CountryID = 35 and YearID = 5
END
I had a go at creating a UDF based on the above and I get warnings around
the IF statement when creating the function. Is it possible to have
conditions in the UDF.
thanks,
jpYou can have IF in a multi-statement table values function but as you say yo
u got en error, I assume
you are trying an in-line table valued function. You should try to make your
functions in-line as
they can potentially perform much better. But why not use only one SELECT st
atement and use CASE
inside that to do what you want?
CREATE TABLE ExchangeRates (Euro money NULL, Dollar money NULL)
INSERT INTO ExchangeRates VALUES(10, 12)
INSERT INTO ExchangeRates VALUES(NULL, 14)
INSERT INTO ExchangeRates VALUES(15, NULL)
INSERT INTO ExchangeRates VALUES(NULL, NULL)
CREATE FUNCTION x (@.iExchangeRate tinyint)
RETURNS table
AS
RETURN
(
SELECT
CASE
WHEN @.iExchangeRate = 1
THEN Euro
ELSE Dollar
END AS ExchangeRate
FROM ExchangeRates
)
SELECT * FROM x(1)
SELECT * FROM x(2)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jp" <jpfrancoisuk@.yahoo.co.uk> wrote in message news:Cs_Gf.3414$gB4.2385@.newsfe4-gui.ntli.
net...
> Hi,
> I've not created one of these before so if anyone can help me get this up
and running that would
> be great. I want to write a function to obtain the exchange rate from a ta
ble based on three
> parameters - exhange rate, country & year.
> The complication I have is I want to pass in the value 1 for Euro and 2 fo
r Dollar so in the
> function I need an "if" statement to run the appropiate select statement.
So in the function it
> would be something like this...
> if @.iExchangeRate = 1
> BEGIN
> select Euro from ExchangeRates where CountryID = 35 and YearID = 5
> END
> ELSE
> BEGIN
> select Dollar from ExchangeRates where CountryID = 35 and YearID = 5
> END
> I had a go at creating a UDF based on the above and I get warnings around
the IF statement when
> creating the function. Is it possible to have conditions in the UDF.
> thanks,
> jp
>|||Thanks - got it working with your help.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23btVNfjLGHA.3960@.TK2MSFTNGP09.phx.gbl...
> You can have IF in a multi-statement table values function but as you say
> you got en error, I assume you are trying an in-line table valued
> function. You should try to make your functions in-line as they can
> potentially perform much better. But why not use only one SELECT statement
> and use CASE inside that to do what you want?
> CREATE TABLE ExchangeRates (Euro money NULL, Dollar money NULL)
> INSERT INTO ExchangeRates VALUES(10, 12)
> INSERT INTO ExchangeRates VALUES(NULL, 14)
> INSERT INTO ExchangeRates VALUES(15, NULL)
> INSERT INTO ExchangeRates VALUES(NULL, NULL)
> CREATE FUNCTION x (@.iExchangeRate tinyint)
> RETURNS table
> AS
> RETURN
> (
> SELECT
> CASE
> WHEN @.iExchangeRate = 1
> THEN Euro
> ELSE Dollar
> END AS ExchangeRate
> FROM ExchangeRates
> )
> SELECT * FROM x(1)
> SELECT * FROM x(2)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "jp" <jpfrancoisuk@.yahoo.co.uk> wrote in message
> news:Cs_Gf.3414$gB4.2385@.newsfe4-gui.ntli.net...
>