June 14, 2012 at 9:01 am
I need to get exchange rate from table. Parameters will be exchange type and date. return will be float
As I cannot use dynamic sqls in function I wrote a stored procedure:
CREATE proc [dbo].[ExRateProc] (@extype nvarchar(10) , @dt nvarchar(50), @exrt as float OUTPUT)
AS
BEGIN
DECLARE @S nvarchar(2000)
set @S = 'SELECT TOP (1) ' + @extype + ' FROM dbo.ExRates WHERE (ratedate<= ''' + @dt + ''') AND (ISNULL(' + @extype + ', 0) <> 0) ORDER BY ratedate desc'
EXEC sp_executesql @S
END
which works perfectly well.
then I wrote a function:
CREATE FUNCTION [dbo].[FindExRate] ( @extp nvarchar(10), @dt nvarchar(50) )
RETURNS float
AS BEGIN
declare @S nvarchar(4000)
DECLARE @exrt float
exec [ExRateProc] @extype = @extp, @dt= @dt, @exrt = @exrt OUTPUT
RETURN @exrt
END
when I set parameters and execute above code in MMS like:
DECLARE @exrt float
EXEC [dbo].[ExRateProc]
@extype = N'USD',
@dt = N'2006-01-03 00:00:00',
@exrt = @exrt OUTPUT
it works well. but when I get in a function, it creates a function but when I call it like:
select [dbo].[FindExRate] ('USD', '2006-01-03 00:00:00')
Only functions and some extended stored procedures can be executed from within a function.
brief question is:
How can I return value with function that comes from dynamic sql ?
I can be more specific:
How can I get exchange rate for specific date ?
any suggestions please ?
June 14, 2012 at 9:05 am
can you give us the CREATE TABLE definition of dbo.ExRates and a few sample rows?
i suspect you could do this with a pivot, but i'd have to see the table to know.
Lowell
June 14, 2012 at 9:35 am
aykut canturk (6/14/2012)
<<snip>>How can I get exchange rate for specific date ?any suggestions please ?
Is USD an exchange type or a currency?
Does "Exchange Type" really mean "Currency"?
What do you have to do to get a new foreign currency into your exchange rates table? What are you going to do when Spain drops the Euro for New Pesetas, Greece for New Drachmas and Italy for New Bribes Lira?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 14, 2012 at 10:40 am
here's table definition
CREATE TABLE [dbo].[ExRates](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ratedate] [date] NULL,
[USD] [float] NULL,
[EURO] [float] NULL
) ON [PRIMARY]
GO
insert into ExRates (ratedate, USD,EURO) values ('01-01-2012', 2.1 , 1.8)
insert into ExRates (ratedate, USD,EURO) values ('01-02-2012', 2.2 , 1.75)
insert into ExRates (ratedate, USD,EURO) values ('01-03-2012', 2.3 , 1.79)
I have only 2 exchange types, USD and EURO. they are also column names. my application's other parts filling the table.
I can of course use pivots, joins, selects in my queries as I currently do but that I need is a function to use like that:
select OrderNo, OrderDate, UnitPrice, UnitExchange, myConvertFuncton('USD', Orders.OrderDate) AS ConvertedPrice From Orders....
June 14, 2012 at 11:03 am
you can use a case statement to return a specific column value based ont heparameter passed:
/*
--Results
2.3
1.79
*/
SELECT [dbo].[ExRateProc]('USD',GETDATE()) As Results
SELECT [dbo].[ExRateProc]('oops',GETDATE()) As Results
CREATE TABLE [dbo].[ExRates](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ratedate] [date] NULL,
[USD] [float] NULL,
[EURO] [float] NULL
) ON [PRIMARY]
GO
insert into ExRates (ratedate, USD,EURO) values ('01-01-2012', 2.1 , 1.8)
insert into ExRates (ratedate, USD,EURO) values ('01-02-2012', 2.2 , 1.75)
insert into ExRates (ratedate, USD,EURO) values ('01-03-2012', 2.3 , 1.79)
GO
CREATE FUNCTION [dbo].[ExRateProc] (@extype nvarchar(10) , @dt nvarchar(50))
RETURNS float
AS
BEGIN
DECLARE @exrt float
SELECT TOP 1
@exrt = CASE
WHEN @extype = 'USD'
THEN USD
ELSE EURO
END
FROM dbo.ExRates WHERE (ratedate <= @dt )
ORDER BY ratedate desc
RETURN @exrt
END
GO
SELECT [dbo].[ExRateProc]('USD',GETDATE()) As Results
SELECT [dbo].[ExRateProc]('oops',GETDATE()) As Results
Lowell
June 14, 2012 at 11:04 am
For 2 currencies - skip the dynamic SQL altogether.
CREATE FUNCTION [dbo].[FindExRate] ( @extp nvarchar(10), @dt nvarchar(50) )
RETURNS float
AS BEGIN
declare @S nvarchar(4000)
DECLARE @exrt float
if @extp='USD'
BEGIN
set @exrt= ( SELECT TOP (1) [USD] FROM dbo.ExRates WHERE (ratedate<= cast(@dt as datetime)) AND (ISNULL([USD] , 0) <> 0) ORDER BY ratedate desc)
END
ELSE
BEGIN
set @exrt= ( SELECT TOP (1) [EURO] FROM dbo.ExRates WHERE (ratedate<= cast(@dt as datetime)) AND (ISNULL([EURO] , 0) <> 0) ORDER BY ratedate desc)
END
RETURN @exrt
END
No procs and no dynamic SQL needed.
For more than that, I would look at normalizing your EXRATES table or performing an UNPIVOT on the fly within the function.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 14, 2012 at 11:06 am
wow, that was cool.... thank you with respect 🙂
June 14, 2012 at 11:07 am
Lowell (6/14/2012)
you can use a case statement to return a specific column value based ont heparameter passed:/*
--Results
2.3
1.79
*/
SELECT [dbo].[ExRateProc]('USD',GETDATE()) As Results
SELECT [dbo].[ExRateProc]('oops',GETDATE()) As Results
CREATE TABLE [dbo].[ExRates](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ratedate] [date] NULL,
[USD] [float] NULL,
[EURO] [float] NULL
) ON [PRIMARY]
GO
insert into ExRates (ratedate, USD,EURO) values ('01-01-2012', 2.1 , 1.8)
insert into ExRates (ratedate, USD,EURO) values ('01-02-2012', 2.2 , 1.75)
insert into ExRates (ratedate, USD,EURO) values ('01-03-2012', 2.3 , 1.79)
GO
CREATE FUNCTION [dbo].[ExRateProc] (@extype nvarchar(10) , @dt nvarchar(50))
RETURNS float
AS
BEGIN
DECLARE @exrt float
SELECT TOP 1
@exrt = CASE
WHEN @extype = 'USD'
THEN USD
ELSE EURO
END
FROM dbo.ExRates WHERE (ratedate <= @dt )
ORDER BY ratedate desc
RETURN @exrt
END
GO
SELECT [dbo].[ExRateProc]('USD',GETDATE()) As Results
SELECT [dbo].[ExRateProc]('oops',GETDATE()) As Results
Nice, but it doesn't handle the case when you add this:
insert into ExRates (ratedate, USD,EURO) values ('01-06-2012', NULL , 1.79)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 14, 2012 at 11:53 am
actually null values not my concern (all values are zero or something) but I want to learn howto use isnull function in this kind of function as it will present using column name in sql function as variable 🙂
thanks.
June 15, 2012 at 2:06 am
iTVF equivalent:
CREATE FUNCTION GetExchangeRate
(@extype nvarchar(10), @dt nvarchar(50))
RETURNS TABLE
AS
RETURN
(
SELECT TOP 1 -- ratedate, x.Currency, -- testing only
exrt = x.Rate
FROM ExRates
CROSS APPLY(VALUES ('USD',USD), ('EURO',EURO)) x (Currency, Rate)
WHERE x.Currency = @extype AND ratedate <= CONVERT(DATE, @dt, 120)
ORDER BY ratedate DESC
)
GO
SELECT exrt FROM dbo.GetExchangeRate('USD', '2012-01-02 00:00:00')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply