need a way around a cursor. - 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

SQL Server Development (2000)

need a way around a cursor.

Author

Topic

inkntation
Yak Posting Veteran

54 Posts

Posted - 2001-06-27 : 09:51:08

I have query that I was told to simply "put in a stored procedure, its timing out in production trying to get an exclusive lock on the table". Ok, The query itself looked fine to me..functionality wise, so i pretty much just put it into an sp. Now they tell me that they want it to be cursor based and each rowupdate be a transaction so that it wont try for an exclusive lock on the whole table. It can update anywhere from 20 to 1000+ rows per run.. although I think it averages a few hundred.
Here the stored proc that I have now:
CREATE PROCEDURE sp_UpdateStatusID @IntYear int,
@IntMonth int,
@IntDay int

AS

BEGIN TRAN

UPDATE db..UsgSvc
SET StatusID = 4
FROM db..UsgSvc U,
db..UsgSvcUser UU,
db..Customer C,
PIC_Company PC
WHERE UU.UsgSvcID = U.UsgSvcID
AND UU.MostRecent = 1
AND C.BillingGroupID = PC.BillingGroupID
AND PC.CompanyID <> 'B'
AND C.CustID = U.CustID
AND U.DisconnectDate is NULL
AND U.SvcTypeID = 1
AND C.AccountStatus = 1
AND C.DisconnectDate is NULL
AND U.ServiceNumber in (SELECT DISTINCT ANI
FROM ProvisionLog,
PIC_Company
WHERE DAY(TransactionDate) = @IntDay
AND MONTH(TransactionDate) = @IntMonth
AND YEAR(TransactionDate) = @IntYear
AND ProvisionLog.BillingGroupID = PIC_Company.BillingGroupID
AND TCSIcode IN (SELECT TCSI
FROM TCSI
WHERE TCSIAction = '01'
OR TCSIAction = '03'))

IF @@error <> 0
BEGIN
ROLLBACK TRAN
END

ELSE

COMMIT TRAN

My understanding is that cursors are a Very Bad Thing (tm). How should I handle this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources