Function used in procedure
Hi all,
I am using a function to reterive the Account no ( total account no count 30,000) and use the account for some other processing in the Stored proc - my doubt is will function gives performnace hit in the SP or instead of using the function what is the other option pls let me know...... here is my SP
CREATE PROCEDURE [dbo].uspGenerateAccount
SELECT
AccountId ,
dbo.fnFYear(AccountId,getdate()) as RYear,
INTO #TempTable
FROM
tblAccounts AP
WHERE NOT Exists ( SELECT
1
FROM
tblClist CL
WHERE
CL.AccountNumber = AP.AccountId
AND
CL.RYear = dbo.fnFYear(AP.AccountId,getdate())
INSERT INTO
CList
(
AccountNumber
,RYear
,AReminder
)
SELECT
AccountID
,RYear
,1
FROM
#TempTable
.
..
..
...
and the function is
ALTER FUNCTION dbo.fnFYear(@AccountID int, @CurrentDate datetime)
RETURNS INT
AS
BEGIN
DECLARE @FiscalYearEnds varchar(5)
DECLARE @FiscalYear INT
SET @FiscalYearEnds='12/31' --DEFAULT YEAR END
SELECT
@FiscalYearEnds = CASE WHEN FiscalYearEnds is NULL
THEN cast(month(max(Statementdate)) as varchar) + '/'
+ cast(day(max(Statementdate)) as varchar)
WHEN ltrim(rtrim(FiscalYearEnds))=''
THEN
cast(month(max(Statementdate)) as varchar) + '/'
+ cast(day(max(Statementdate)) as varchar)
ELSE
ltrim(rtrim(FiscalYearEnds))
END
FROM
Statement st
INNER JOIN
vwOrganization vw
ON
st.fkPartyID = vw.PartyID
WHERE
vw.AccountID = @AccountID
and fkStatusCodeID = 1 ---Fiscal
GROUP BY FiscalYearEnds
SELECT @FiscalYearEnds = dbo.fnValidFYE(@FiscalYearEnds)
IF @CurrentDate > DATEADD(DAY,1,CAST(YEAR(@CurrentDate) -1 as varchar(4)) + '/' + @FiscalYearEnds)
and
@CurrentDate < DATEADD(DAY,1,CAST(YEAR(@CurrentDate) as varchar(4)) + '/' + @FiscalYearEnds)
SET @FiscalYear= CAST(YEAR(@CurrentDate)-1 AS INT)
ELSE
SET @FiscalYear= CAST(YEAR(@CurrentDate) AS INT)
RETURN @FiscalYear
END
|
