Transaction in stored procedures nested in cursor

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)

Transaction in stored procedures nested in cursor

Author

Topic

bigbelly
Starting Member

39 Posts

Posted - 2006-02-06 : 21:13:48

Hi experts

I have a job calling a stored procedure A. Inside that stored procedure A, there is a cursor looping through a table to get pending actions. then according to those pending actions type, call different stored procedures.
The stored procedure A looks like :

CREATE PROCEDURE DBO.storedproA
AS
BEGIN
DECLARE @TransID INT
DECLARE @TransTypeID INT
DECLARE @UserAdminID INT

DECLARE CurTransLog CURSOR LOCAL
FOR
SELECT TransID, TransTypeID, UserAdminID
FROM TransLog
WHERE ISNULL(PendingStatus, 0) = 0

OPEN CurTransLog

FETCH NEXT FROM CurATransLog
INTO @TransID, @TransTypeID, @UserAdminID

WHILE @@FETCH_STATUS = 0
BEGIN
IF @TransTypeID = 1
BEGIN
EXEC case1_sp @TranID, @intUserAdminID
END
ELSE IF @TransactionTypeID = 2
BEGIN
EXEC case2_sp @TransID, @intUserAdminID
END
ELSE IF @TransTypeID = 3
BEGIN
EXEC case3_sp @TransID, @UserAdminID
END
...............
...............
...............
ELSEIF @TransTypeID = 11
BEGIN
EXEC case11_sp @TransID, @UserAdminID
END

FETCH NEXT FROM CurPDATransLog
INTO @TransID, @TransTypeID, @UserAdminID

END

CLOSE CurTransLog
DEALLOCATE CurTransLog
END

In those nested stored procedures: case1_sp, case2_sp...case11_sp.,
I use transaction. either commit or rollback.

The question is : in this situation(transaction within stored procedures which are nested inside a cursor),
will it create lots of exclusive locks? The reason I'm asking is when I monitor this job, I was checking using sp_lock command and found in the middle of job execution, there are around 10 time exclusive locks of my pending actions(say, i have 140 pending actions, i found there would be around 1400 something exclusive locks in the middle of job execution).

Can anyone give me some idea about this issue? Do I have a way to improve my job? thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-06 : 21:30:16

Well get rid of the cursor then
Can select the rows needed into a temp table with an identity and use the identity to loop through the temp table.
For calling the SPs I would probably hold all the SP names in a table variable then call them via dynamic sql if permissions allow that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources