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
Without the DDL for the table and some sample data I can't really say, but it doesn't appear to me that this procedure will work at all.
Just from looking at it, it appears that you are attempting to delete all the rows from the table. If this is so, there are two ways to accomplish this.
The first is fully logged:
DELETE FROM test;
The second isnt:
TRUNCATE TABLE test;
Please let us know what it is you are actually trying to accomplish. Also, you should also read the article I have linked below in my signature block. It provides good advice on how to ask for help that will provide you the best answers.