SQL iterating over a list to call EXEC on each item

Question

Attempt to generalize my questions... I want to execute a stored procedure for each result returned by a SELECT statement.

Mentally I want to try something like EXEC myStoredProc (SELECT id FROM sometable WHERE cond = @param)

More details related to my specific case... I have a SaaS application. I would like to delete a tenant from the system. Before I can delete the tenant I must delete all records in the database associated with that tenant.

Tenants own items such as Forms which contain many different types of Fields. I already have a stored proc that deletes a Form and all of its associated items (like Fields). For maintenance reasons (Ie. not wanted to duplicate the logic that determines dependances and associations between records and a form) I'd like to just call that StoredProc for each Form that belongs to a Tenant.

I can retrieve the list of forms by running a query like... Select formId FROM Forms WHERE Tenant = @TenantId

What I want to do with the result of that query is EXEC my Delete_Form stored procedure.

How can I do this?

Answer 1

You could just turn Cascade delete on and deleting the parent record would delete all child records associated with it.

If not you'll have to create a cursor (The link is for sql server, but I would assume that cursors for other RDBMS are similar) and loop through each of the results pulling out the form id and executing the [Delete_Field_Procedure] for each one.

Answer 2

In the case where you don't have control of the foreign keys and can't do cascade deletes, you could create a cursor to loop thru and execute the stored proc for each.

declare @formID int
declare FormsCursor cursor fast_forward for Select formId FROM Forms WHERE Tenant = @Tenant

open FormsCursor

fetch next from FormsCursor into @formID

while @@fetch_status = 0
begin

   exec Delete_Form @formID

   fetch next from FormsCursor into @formID

end

close FormsCursor
deallocate FormsCursor