avoiding cursors 1

[eja2000]

i have used a cursor to process data in a table based on data in another table..i know they are bad and i want to now use tsql astatements.
can you pls point me to some example of tsql statements online where i can update data in a table based on acondition.
thx

 

[AtomicWedgie]

Can you give your cursor statement as example?

 

[eja2000]

CREATE proc spActivateAccts
as

set nocount on

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

DECLARE fileCursor Cursor for
SELECT SequenceID,pincode ,msisdn,NoOfMonths,startdate FROM vActivateAccts
OPEN fileCursor

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


UPDATE TempDetails

SET Pincode = @pincode,
AcctStatus = 'A',
period_from = @startdate,
msisdn ='0'+ RIGHT(@msisdn,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 (msisdn=@msisdn) AND (transactype='N') AND (AcctStatus ='A')

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

DELETE FROM RegisteredSubscribers WHERE (TransactType='N') AND (SuccessFlag ='S')

 

[AtomicWedgie]

UPDATE TempDetails
SET TempDetails.Pincode = vActivateAccts.Pincode
, TempDetails.AcctStatus = 'A'
, TempDetails.period_from = vActivateAccts.Startdata
, TempDetails.msisdn = '0' + RIGHT(vActivateAccts.msisdn,10)
FROM vActivateAccts, TempDetails
WHERE TempDetails.sequenceid = vActivateAccts.sequenceid

I am not sure if you need to define the TempDetails table in the from clause.

 

[eja2000]

thanks atomicwedgie,

quick question;

1) the WHERE part will actually read;
WHERE TempDetails.msisdn = vActivateAccts.msisdn

the msisdn which links TempDetails and vActivateAccts is also part of the update statement.
is this ok? will the update work as it should?..

thanks a lot..

 

[AtomicWedgie]

Hi,

i think it should work but in your update you set the Tempdetails.msisdn to '0' + RIGHT(vActivateAccts.msisdn,10).

I am just wondering if the next time you run the update the msisdn in your where clause will give you the right records to update.

Example:

first time you update

Tempdetails.msisdn = 123456
vActivateAccts.msisdn = 123456

Tempdetails.msisdn updates to 0123456

second time you update

Tempdetails.msisdn = 0123456
vActivateAccts.msisdn = 123456

There will be no update because id's do not match.

Don't know if my assumption is correct but maybe you should take a look at it.

 

[eja2000]

so i can join a view to a table?

 

[AtomicWedgie]

why not

 

[eja2000]

thanks atomicwedgie..

eliminating cursors has made my app much faster..