Nandy (11/18/2008)

---

Hi All,

I have used the cursor in the stored procedure to delete the data from the table. I'm deleting the data from table where I used the same table for selecting using cursor. I'm not able to delete the data from the table, however if I run only the cursor I can able to delete the data. If i use the cursor inside the procedure am not able to delete. Please can anyone suggest me on the same. Below is the procedure which I have written.

CREATE Procedure DBPSRemoveTest_Sp

As

Begin

DECLARE TEST_CURSOR CURSOR FOR

SELECT * FROM Test

DECLARE @RetPeriodint

DECLARE @AuthIdVarchar(50)

OPEN TEST_CURSOR

FETCH NEXT FROM TEST_CURSOR

INTO @RETPERIOD,@AUTHID

WHILE @@FETCH_STATUS=0

BEGIN

BEGIN

delete from test where userid=@RetPeriod and Username=@Authid

IF(@@ROWCOUNT=0)

PRINT 'Failed to delete the row from the table'

END

FETCH NEXT FROM TEST_CURSOR

INTO @RETPERIOD,@AUTHID

END

CLOSE TEST_CURSOR

DEALLOCATE TEST_CURSOR

end

GO

Cheers,

Nandy