commit not commiting inside stored procedure

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

General SQL Server Forums

New to SQL Server Programming

commit not commiting inside stored procedure

Author

Topic

setsun1
Starting Member

3 Posts

Posted - 2006-06-23 : 05:22:14

Hi Folks!

I have a cursor inside a stored procedure that should identify rows based on certain criteria and update the columns on those records.

While I try to do so, the cursor goes into an infinite loop although I have only 1 record that matches my criteria. I think that the transaction is not commited and the cursor is picking up the same record for update.

Here is my sample record in table :tblMsg
SenderRef <space> MsgStatusId <space> MsgType <space>Groupid
1281341<space> 1 <space>KBC-NON-ETC-MATCHED-BLIM <space>191902

1281341 <space>18 <space>KBC-RCVD-ADM <space>191902<space>

and here is my code.

CREATE PROCEDURE msg_ResolveADMBeforeBlim
AS

DECLARE @blimGroupId AS INT
DECLARE @admSenderRef AS VARCHAR(50)

DECLARE admCursor CURSOR FOR
SELECT senderref
FROM tblMsg
WHERE msgstatusid = 18
AND msgtype = 'KBC-RCVD-ADM'
FOR UPDATE

OPEN admCursor

FETCH NEXT FROM admCursor INTO @admSenderRef

WHILE @@FETCH_STATUS = 0
BEGIN
--FIND CORRESPONDING BLIM RECORD AND GRAB ITS GROUPID TO UPDATE THE ADM
SELECT @blimGroupId = GroupID
FROM tblMsg
WHERE msgType = 'KBC-NON-ETC-MATCHED-BLIM'
AND SenderRef = @admSenderRef
PRINT 'AFTER SELECT INSIDE WHILE: Senderref- >' + @admSenderRef;
--UPDATE THE ADM RECORD WITH THE GROUPID AND MSGSTATUS = 3
BEGIN
UPDATE tblMsg
SET MsgStatusId = 3, GroupId = @blimGroupId
WHERE CURRENT OF admCursor
END
PRINT 'AFTER UPDATE INSIDE WHILE';
END

CLOSE admCursor
DEALLOCATE admCursor

The update statement was included beteween a BEGIN TRAN and END TRAN. But no joy.

Any ideas why this would happen?

Thanks for your help.
Arun

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-23 : 05:26:04

"The update statement was included beteween a BEGIN TRAN and END TRAN. But no joy. "
END TRAN ? ? should be


BEGIN TRAN
. . . 
COMMIT TRAN

setsun1
Starting Member

3 Posts

Posted - 2006-06-23 : 05:43:10

quote:
Originally posted by khtan

"The update statement was included beteween a BEGIN TRAN and END TRAN. But no joy. "
END TRAN ? ? should be

BEGIN TRAN
. . .
COMMIT TRAN

KH

setsun1
Starting Member

3 Posts

Posted - 2006-06-23 : 05:45:19

Oops. I meant COMMIT TRAN.

But anyways I have identified the blunder. I never fetched the next record from the cursor.

I included "FETCH NEXT FROM admCursor INTO @admSenderRef" after the update and it worked.

Thanks for looking into this.

Arun

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources