存储过程 - 强制执行顺序 [英] Stored Procedure - forcing execution order

问题描述

我有一个存储过程，它本身按顺序调用其他存储过程的列表:

I have a stored procedure that itself calls a list of other stored procedures in order:

CREATE PROCEDURE [dbo].[prSuperProc]

AS
BEGIN
    EXEC [dbo].[prProc1] 
    EXEC [dbo].[prProc2] 
    EXEC [dbo].[prProc3]
    --etc
END

但是，我的表中有时会出现一些奇怪的结果，由 prProc2 生成，这取决于 prProc1 生成的结果.如果我按顺序手动执行 prProc1、prProc2、prProc3，那么一切都很好.看来，当我运行顶级过程时，在 Proc1 完成并将其结果提交给数据库之前，正在执行 Proc2.它并不总是出错，但是当 Proc1 的执行时间很长(在本例中为 ~10s)时，它似乎出错了.

However, I sometimes have some strange results in my tables, generated by prProc2, which is dependent on the results generated by prProc1. If I manually execute prProc1, prProc2, prProc3 in order then everything is fine. It appears that when I run the top-level procedure, that Proc2 is being executed before Proc1 has completed and committed its results to the db. It doesn't always go wrong, but it seems to go wrong when Proc1 has a long execution time (in this case ~10s).

如何更改 prSuperProc 以便每个过程仅在前一个过程完成并提交后执行?交易?

How do I alter prSuperProc such that each procedure only executes once the preceding procedure has completed and committed? Transactions?

编辑以获取更多详细信息:

我的数据库中有一个表，它有一列默认为空.prProc1 对该表执行一组更新语句以填充该列.prProc2 然后根据此列中的值将汇总数据插入到辅助表中.

There is a table in my db which has a column which is null by default. prProc1 performs a set of update statements on this table to populate this column. prProc2 then inserts summary data into a secondary table based on the values in this column.

当我运行超级过程时，我(有时)看到的是第一个表的结果由 prProc1 正确计算，但 prProc2 生成的结果好像该列都是空值.如果我随后手动运行 prProc2，则会正确生成摘要数据.

When I run the super procedure, what I am (sometimes) seeing is the first table has the results correctly calculated by prProc1, but prProc2 has generated results as though the column was all nulls. If I then manually run prProc2, the summary data is generated correctly.

推荐答案

Proc2 将不会在 Proc1 之前运行:就这么简单.SQL 会一个接一个执行，但不会乱序.

Proc2 will not run before Proc1: it's a simple as that. SQL will execute one after the other but never out of order.

您可以使用 TSQL_SPs 模板

例如，您是否有 2 次执行的包装程序正在运行?

Do you have 2 executions of the wrapper proc running, for example?

这篇关于存储过程 - 强制执行顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！