Running Stored Procedure with parameters resulting from query

Question

It's not hard to find developers who think cursors are gauche but I am wondering how to solve the following problem without one:

Let's say I have a proc called uspStudentDelete that takes as a parameter @StudentID.

uspStudentDelete applies a bunch of cascading soft delete logic, marking a flag on tables like "classes", "grades", and so on as inactive. uspStudentDelete is well vetted and has worked for some time.

What would be the best way to run uspStudentDelete on the results of a query (e.g. select studentid from students where ... ) in *TSQL*?

Answer 1

That's exactly what cursors are intended for:

declare c cursor local for <your query here>
declare @ID int
open c
fetch next from c into @id
while @@fetch_status = 0 
    begin
    exec uspStudentDelete @id
    fetch next from c into @id
    end
close c
deallocate c

Most people who rail against cursors think you should do this in a proper client, like a C# desktop application.

Answer 2

The best solution is to write a set-based proc to handle the delete (try running this through a cursor to delete 10,000 records and you'll see why) or to add the set-based code to the current proc with a parameter to tell you wheter to run the set-based or single record part of the proc (this at least keeps it together for maintenance purposes).

In SQL Server 2008 you can use a table variable as an input variable. If you rewrite the proc to be set-based, you can have the same logic and run it no matter if the proc sends in one record or ten thousand. You may need to have a batch process in there to avoid deleting millions of records in one go though and locking up the tables for hours. Of course if you do this you will also need to adjust how the currect sp is being called.