SQL iterating through a list to call EXEC on each item

Question

Trying to summarize my questions ... I want to execute a stored procedure for each result returned by the SELECT statement.

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

More about my specific case ... I have a SaaS application. I would like to remove the tenant from the system. Before I can remove the tenant, I must delete all the entries in the database associated with this tenant.

Tenants have items such as Forms that contain many different types of fields. I already have a saved proc that deletes the form and all its related elements (e.g. fields). For maintenance reasons (i.e., I didn’t want to duplicate the logic that defines the dependencies and associations between the records and the form). I would simply name StoredProc for each Form owned by the Tenant.

I can get a list of forms by running a query such as ... Select formId FROM Forms WHERE Tenant = @TenantId

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

How can i do this?

Answer 1

In the case when you do not have control over foreign keys and cannot perform cascading deletions, you can create a cursor for the loop and execute a saved process 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 

Answer 2

You can simply enable Cascade delete and delete the parent record to delete all the child records associated with it.

If not, you will need to create a cursor (the link for the sql server, but I would assume that the cursors for other DBMSs are similar) and cycle through each of the results, pulling out the form identifier and executing [Delete_Field_Procedure] for each of them.