How can I use the cursor to delete a record from a table

Question

I want to use the cursor to delete a record from a table. How can i do this?

I am using MSSQL 2008 Express, this code does not remove anything from #temp. I also tried when the cursor_name current did not work.

Here is my sample code:

use AdventureWorks drop table #temp select * into #temp from HumanResources.Employee; declare @eid as int; declare @nid as varchar(15); DECLARE Employee_Cursor CURSOR FOR SELECT A.EmployeeID, A.NationalIDNumber FROM #temp AS A OPEN Employee_Cursor; FETCH NEXT FROM Employee_Cursor INTO @eid , @nid ; WHILE @@FETCH_STATUS = 0 BEGIN IF (@eid > 10) BEGIN delete from #temp where #temp.EmployeeID = @eid; END FETCH NEXT FROM Employee_Cursor; END; CLOSE Employee_Cursor; DEALLOCATE Employee_Cursor; GO select * from #temp 

early

Answer 1

 use AdventureWorks select * into #temp from HumanResources.Employee; declare @eid as int; declare @nid as varchar(15); DECLARE Employee_Cursor CURSOR FOR SELECT A.EmployeeID, A.NationalIDNumber FROM #temp AS A OPEN Employee_Cursor; FETCH NEXT FROM Employee_Cursor INTO @eid , @nid ; WHILE @@FETCH_STATUS = 0 BEGIN IF (@eid > 10) BEGIN delete from #temp where current of Employee_Cursor END FETCH NEXT FROM Employee_Cursor INTO @eid , @nid ; END; CLOSE Employee_Cursor; DEALLOCATE Employee_Cursor; select * from #temp drop table #temp 

it works for me

Answer 2

The answer is much simpler: use this command:

 delete from HumanResources.Employee where current of Employee_Cursor 

It is called "Situated Removal" and is described on MSDN .

Answer 3

Could you try to find ways, thanks for your time.

You extracted data from the cursor, but did not click on the variables skipped in the WHILE loop, please see the code below, thanks.

 drop table #temp select * into #temp from Employee; declare @eid as int; declare @nid as varchar(15); DECLARE Employee_Cursor CURSOR FOR SELECT A.EmployeeID, A.NationalIDNumber FROM #temp AS A OPEN Employee_Cursor; FETCH NEXT FROM Employee_Cursor INTO @eid , @nid ; WHILE @@FETCH_STATUS = 0 BEGIN IF (@eid > 10) BEGIN delete from #temp where #temp.EmployeeID = @eid; END FETCH NEXT FROM Employee_Cursor INTO @eid , @nid ; END; CLOSE Employee_Cursor; DEALLOCATE Employee_Cursor; GO select * from #temp 

Update: I made changes to the 2nd FETCH statement, just added the highlighted part below, thanks

FETCH NEXT FROM Employee_Cursor INTO @eid, @nid ;