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)
与我的具体案例相关的更多细节...
I有一个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.
租户拥有的项目,例如包含许多不同类型的字段的表单。我已经有一个存储过程删除一个Form及其所有相关项(如Fields)。出于维护原因(即不想复制确定记录和表单之间的依赖关系和关联的逻辑)我想为每个属于租户的表单调用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.
我可以通过运行查询来检索表单列表,例如...
选择formId FROM Forms WHERE Tenant = @TenantId
I can retrieve the list of forms by running a query like... Select formId FROM Forms WHERE Tenant = @TenantId
什么我想要查询的结果是EXEC我的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屋!