How can I return value from function that comes from dynamic sql

Question

Post reply

How can I return value from function that comes from dynamic sql

aykut canturk

SSC Eights!

Points: 870
More actions
June 14, 2012 at 9:01 am

#257602

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-2 nvarchar(2000)
set @s-2 = 'SELECT TOP (1) ' + @extype + ' FROM dbo.ExRates WHERE (ratedate<= ''' + @dt + ''') AND (ISNULL(' + @extype + ', 0) <> 0) ORDER BY ratedate desc'
EXEC sp_executesql @s-2
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-2 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 ?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

aykut canturk SSC Eights! Points: 870 More actions · Answer 3

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

Lowell SSC Guru Points: 323518 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

For 2 currencies - skip the dynamic SQL altogether.

CREATE FUNCTION [dbo].[FindExRate] ( @extp nvarchar(10), @dt nvarchar(50) )

RETURNS float

AS BEGIN

declare @s-2 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?

aykut canturk SSC Eights! Points: 870 More actions · Answer 6

aykut canturk

SSC Eights!

Points: 870

June 14, 2012 at 11:06 am

#1501188

wow, that was cool.... thank you with respect 🙂

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

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?

aykut canturk SSC Eights! Points: 870 More actions · Answer 8

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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