Expanding functions within a script

Question

Expanding functions within a script

chas.clarke

SSC Rookie

Points: 28
More actions
June 10, 2011 at 9:24 am

#389706

Can someone possibly help me?
I want expand 2 functions within my script so that I don't have to call any functions (or stored procedures).
The line within my SELECT currently looks like this:
[font="Courier New"]Opening_Overhead_USD_CR = ISNULL(f1.Overhead,0)*rpt.getFXRate(f1.Currency,rpt.GetMonthEnd(DATEPART(YEAR,(DATEADD(MONTH,-1,@sd)))*100+DATEPART(MONTH,(DATEADD(MONTH,-1,@sd))))),
[/font]
So, I'm looking to do away with the [font="Courier New"]rpt.getFXRate[/font] and [font="Courier New"]rpt.GetMonthEnd[/font] functions.
Many thanks,
Chas

Viewing 7 posts - 1 through 6 (of 6 total)

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

I recently was working on doing something similiar. We had a system that was written by someone who was in love with functions and used them for everything. As a result a query that should run in about 2 seconds would run for minutes. At any rate with out seeing the functions it is hard to quess but I would think you need to examine them and in most cases replace that aspect with a join in the query.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

chas.clarke SSC Rookie Points: 28 More actions · Answer 2

If it helps the functions are:

[font="Courier New"]ALTER FUNCTION [rpt].[getFXRate](@Currency CHAR(3), @OnDate DATETIME)

RETURNS FLOAT

AS

BEGIN

-- make sure that we don't do anything for US$

IF @Currency='USD' RETURN 1

DECLARE @Rate FLOAT

-- get the first rate on our date (or first date before that)

SELECT TOP 1 @Rate = FXRate

FROM rpt.FXRate

WHERE Currency=@Currency

AND DATEDIFF(DAY,EffDate,@OnDate)>=0

ORDER BY EffDate DESC

-- if rate on selected date or before not found - find first available rate

IF @@ROWCOUNT=0

SELECT TOP 1 @Rate = FXRate

FROM rpt.FXRate

WHERE Currency=@Currency

ORDER BY EffDate ASC -- if rate for our date is not available - there might be one in the future

IF @Rate IS NULL SET @Rate=1 -- if rateis not available - return 1, so at least we'll get some number instead of NULL (will this create more problem than solve?)

RETURN 1.0/@Rate

END[/font]

and

[font="Courier New"]ALTER FUNCTION [rpt].[GetMonthEnd](@ReportingMonth INT)

RETURNS DATETIME

AS

BEGIN

RETURN DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, STR(@ReportingMonth)+'01')+1, 0))

END

[/font]

Obviously, the second function is fairly straightforward. It's the first one I have the concerns over.

Regards,

Chas

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

chas.clarke (6/10/2011)
Can someone possibly help me?
I want expand 2 functions within my script so that I don't have to call any functions (or stored procedures).
The line within my SELECT currently looks like this:
[font="Courier New"]Opening_Overhead_USD_CR = ISNULL(f1.Overhead,0)*rpt.getFXRate(f1.Currency,rpt.GetMonthEnd(DATEPART(YEAR,(DATEADD(MONTH,-1,@sd)))*100+DATEPART(MONTH,(DATEADD(MONTH,-1,@sd))))),
[/font]
So, I'm looking to do away with the [font="Courier New"]rpt.getFXRate[/font] and [font="Courier New"]rpt.GetMonthEnd[/font] functions.
Many thanks,
Chas

It will make a difference... Are you writing the code for a single line call from a GUI or are you writing code for a report/something similar?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chas.clarke SSC Rookie Points: 28 More actions · Answer 4

Jeff,

Thanks for coming back.

I am trying to combine several stored procedures and functions into one query that I can run with my limited access. Initially, whilst I refine the code, I will be running it in Management Studio but ultimately I will be looking to run it via a click in a GUI.

Thanks again,

Chas

chas.clarke SSC Rookie Points: 28 More actions · Answer 5

Thank you too CELKO for coming back.

Just a copy of questions:

How can I incorporate your suggestion into my code?

Where can I find out more about the Triangular rule you refer to?

Is FLOAT really illegal for these kind of currency conversions?

Thanks again,

Chas

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

chas.clarke (6/13/2011)
Jeff,
Thanks for coming back.
I am trying to combine several stored procedures and functions into one query that I can run with my limited access. Initially, whilst I refine the code, I will be running it in Management Studio but ultimately I will be looking to run it via a click in a GUI.
Thanks again,
Chas

Apoligies... I got caught up in a bunch of other things and this thread slipped through the cracks. I'll try to take a closer look later on tonight.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)