SQL 遍历列表以对每个项目调用 EXEC [英] SQL iterating over a list to call EXEC on each item

问题描述

尝试概括我的问题...我想为 SELECT 语句返回的每个结果执行一个存储过程.

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

心理上我想尝试类似的东西EXEC myStoredProc (SELECT id FROM sometable WHERE cond = @param)

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

与我的具体案例有关的更多详细信息...我有一个 SaaS 应用程序.我想从系统中删除一个租户.在我可以删除租户之前，我必须删除与该租户关联的数据库中的所有记录.

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.

租户拥有诸如表单之类的项目，其中包含许多不同类型的字段.我已经有一个存储过程，可以删除表单及其所有关联项目(如字段).出于维护原因(即不想重复确定记录和表单之间的依赖关系和关联的逻辑)，我只想为属于租户的每个表单调用该 StoredProc.

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.

我可以通过运行类似...的查询来检索表单列表从 Forms WHERE Tenant = @TenantId 中选择 formId

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

我想要对查询结果执行的操作是执行我的 Delete_Form 存储过程.

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

我该怎么做?

推荐答案

在你无法控制外键并且不能进行级联删除的情况下，你可以创建一个游标来循环并执行为每个存储过程.

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

这篇关于SQL 遍历列表以对每个项目调用 EXEC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！