Posted by Ed Murphy on 12/11/06 16:51

Patti wrote:

> 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

Untested:

insert into tblInjectLoans (lnkey, AuditorID)
select lnkey, AuditorID
from tblALPSLoans al
where AuditDate between @BegDate and @EndDate
and AuditorID in (
select lanid
from tblEmployees
where actiontype = 'ADDED'
)
and AuditType = @AuditType
and (select count(lnkey)
from tblInjectLoans il
where il.AuditorID = al.AuditorID
)
< (select round(count(lnkey) * @Percent/100, 0)
from tblALPSLoans al2
where al2.AuditDate between @BegDate and @EndDate
and al2.AuditorID = al.AuditorID
)

[Back to original message]