Using Function inside the SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using Function inside the SP

Author

Topic

DeepakNewton
Starting Member

39 Posts

Posted - 2008-02-12 : 04:34:07

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 05:00:01

What is the function doíng?

E 12°55'05.25"
N 56°04'39.16"

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-12 : 05:11:31

The other option is to have the actual code in the function as part of the sql statement, this is dependant on the complexity .

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

DeepakNewton
Starting Member

39 Posts

Posted - 2008-02-12 : 23:54:06

Peso,
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

DeepakNewton
Starting Member

39 Posts

Posted - 2008-02-13 : 02:17:04

Any Solutions ??

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2008-02-13 : 02:44:08

Try

INSERT INTO CList (AccountNumber, RYear, AReminder)
SELECT A.AccountID, A.RYear, 1
FROM (SELECT AccountId, dbo.fnFYear(AccountId, getdate()) AS RYear FROM tblAccounts) A
LEFT OUTER JOIN tblClist B ON B.AccountNumber = A.AccountId AND B.RYear = A.RYear
WHERE B.AccountId IS NULL

DeepakNewton
Starting Member

39 Posts

Posted - 2008-02-13 : 04:04:19

koji
i Need to use the #TempTable for several operation inside the same proc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources