cursor to set based query - 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)

cursor to set based query

Author

Topic

fgeorge
Starting Member

6 Posts

Posted - 2004-08-13 : 12:41:44

can anyone help me with converting this to a set based query?
-----------------------------------------------------------------------------

CREATE proc spActivateAccts
as

set nocount on

DECLARE @Sequence int
DECLARE @pincode int
DECLARE @xxx varchar(20)
DECLARE @months_number numeric
DECLARE @startdate datetime

DECLARE fileCursor Cursor for
SELECT top 800 SequenceID,pincode ,xxx,NoOfMonths,startdate FROM vActivateAccts
OPEN fileCursor

FETCH NEXT FROM fileCursor INTO @Sequence ,@pincode ,@xxx,@months_number,@startdate
while @@fetch_status=0
BEGIN

UPDATE TempDetails

SET Pincode = @pincode,
AcctStatus = 'A',
period_from = @startdate,
xxx='0'+ RIGHT(@xxx,10)

WHERE
( SequenceID = @Sequence)

INSERT INTO MasterDetails SELECT * FROM TempDetails WHERE (SequenceID = @Sequence)

UPDATE MasterDetails

SET AcctStatus = 'A',
period_to = (DATEADD(month,@months_number,period_from))

WHERE
( SequenceID = @Sequence)

DELETE FROM TempDetails WHERE (SequenceID = @Sequence)
DELETE FROM RegisteredSubscribers WHERE (xxx=@xxx) AND (TransactType = 'N')AND (Successflag ='S')

FETCH NEXT FROM fileCursor INTO @Sequence ,@pincode ,@xxx,@months_number,@startdate
END
Close fileCursor
Deallocate fileCursor

----------------------------------------
as you can see this processes a max of 800 records at once but it is toooo slow..it takes about 3 minutes to run..

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-13 : 13:05:29

try this:

set rowcount 800

UPDATE t1
SET t1.Pincode = t2.pincode,
t1.AcctStatus = 'A',
t1.period_from = t2.startdate,
t1.xxx='0'+ RIGHT(t2.xxx,10)
FROM TempDetails t1 inner join vActivateAccts t2 on (t1.SequenceID = t2.SequenceID)

INSERT INTO MasterDetails
SELECT t1.*
FROM TempDetails t1
inner join vActivateAccts t2 on (t1.SequenceID = t2.SequenceID)

UPDATE t1
SET AcctStatus = 'A',
period_to = (DATEADD(month, t2.NoOfMonths, period_from))
FROM MasterDetails t1 inner join vActivateAccts t2 on (t1.SequenceID = t2.SequenceID)

DELETE t1
FROM TempDetails t1
inner join vActivateAccts t2 on (t1.SequenceID = t2.SequenceID)

DELETE t1
FROM RegisteredSubscribers t1
inner join vActivateAccts t2 on (t1.xxx = t2.xxx) AND (t1.TransactType = 'N') AND (t1.Successflag ='S')

set rowcount 0

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources