|
|
Posted by Patti on 12/11/06 14:32
I am new to SQL and have created a stored procedure for a .net web
application. Unfortunately I had to use a cursor in the stored
procedure, so it is taking several minutes to execute because it has to
read through about 15,000 records. There must be another way to do
what I'm trying to do without a cursor. I've tried temp tables and
case statements, but I can't seem to get them to work. I've been
trying to figure this out for over a week and I am just running into a
wall. Some expert advise would be much appreciated. My code is below.
Thank you in advance.
--Insert records into first temp table
DECLARE @tempA TABLE
(
lnkey varchar(10),
AuditorID varchar(7)
)
INSERT INTO @tempA
SELECT
LNKEY
,AuditorID
FROM
dbo.tblALPSLoans
WHERE AuditDate BETWEEN @BegDate AND @EndDate --parameters from my
application
AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
'ADDED')
AND AuditType = @AuditType --parameter from my application
--Insert percentage value of Pre-Funding completes for each auditor
into temp table B
DECLARE @tempB TABLE
(
LnkeyCount int,
AuditorID varchar(7)
)
INSERT INTO @tempB
SELECT
ROUND(COUNT(LNKEY) * @Percent/100, 0) AS 'LnkeyCount'
,AuditorID
FROM dbo.tblALPSLoans
WHERE AuditDate BETWEEN @BegDate AND @EndDate
AND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE =
'ADDED')
GROUP BY AuditorID
/*Create cursor to loop through records and add a loan number to
tblinjectloans if the number of loans in tblinjectloans for each
auditor is less than the percentage value for each auditor from
@tempB*/
DECLARE @lnkey varchar(10)
DECLARE @AuditorID varchar(7)
DECLARE @var1 int
DECLARE @var2 int
DECLARE @sql varchar(4000)
DECLARE c1 CURSOR FOR
SELECT lnkey, auditorid
FROM @TempA
OPEN c1
FETCH NEXT FROM c1
INTO @LNKEY, @AuditorID
WHILE @@FETCH_STATUS = 0
BEGIN
Select @var1 = COUNT(Lnkey) from dbo.tblInjectLoans where
AuditorID=@AuditorID
Select @var2 = LnkeyCount from @tempB where AuditorID=@AuditorID
IF @var1 < @var2
Insert into dbo.tblInjectLoans
(lnkey, AuditorID)
Values (@LNKEY, @AuditorID)
FETCH NEXT FROM c1
INTO @LNKEY, @AuditorID
END
CLOSE c1
DEALLOCATE c1
Navigation:
[Reply to this message]
|