Assuming I have table with tasks called Tasks.
I want to delete selected tasks with stored procedure SP_Task_DEL (which deletes tasks).
This procedure have one parameter @Id (id of task to delete).
How to execute this stored procedure many times with all results of query:
select id from Tasks where status = 'completed'
?
This is done with a cursor. Loop over the results and execute the procedure. Do note that this is slow and can most likely be done with one single delete statement.
DECLARE @id int
DECLARE cur_delete CURSOR LOCAL READ_ONLY
FOR select id
from Tasks
where status = 'completed'
OPEN cur_delete
FETCH NEXT FROM cur_delete into @id
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC SP_Task_DEL @id
FETCH NEXT FROM cur_delete into @id
END
CLOSE cur_delete
DEALLOCATE cur_delete
The simplest way to delete all completed tasks is:
DELETE Tasks
where status = 'completed'
If there are more tables to be cleaned out the following pattern needs to be used.
BEGIN TRAN
DELETE SubTasks
FROM SubTasks st
JOIN Tasks t (updlock)
ON st.id = t.id
WHERE t.status = 'completed'
if @@error <> 0
begin
rollback tran
goto THEEND
end
DELETE Tasks
where status = 'completed'
COMMIT TRAN
THEEND:
You could use a cursor for that, like:
declare @id int
declare cur cursor local fast_forward for
select id from Tasks where status = 'completed'
open cur
fetch next from cur into @id
while @@fetch_status = 0
begin
EXEC dbo.SP_Task_DEL @id
fetch next from cur into @id
end
close cur
deallocate cur
Why not create a different stored procedure that deletes all completed tasks? That would be much more efficient as you can take advantage of the fact that databases are really very efficient at dealing with sets of data rather than looping over individual items.
