存储过程调用多个存储过程 [英] Stored Procedure calls multiple stored procedures

问题描述

当为一个存储过程调用多个存储过程时，这是在 SQL Server 2008 上执行此操作的正确方法还是最佳方法?

When calling several stored procedures for one stored procedure is this the right or best way to go about it on SQL Server 2008 ?

CREATE PROCEDURE [dbo].[DoStuff]
AS
BEGIN
    SET NOCOUNT ON;
    declare @result int
    BEGIN TRANSACTION
        BEGIN
            EXECUTE @result = dbo.UpdateTHIS @ID = 1            
            IF @result != 0
                ROLLBACK
            ELSE 
                EXECUTE @result = dbo.UpdateTHAT @ID = 21               
                IF @result != 0
                    ROLLBACK
                ELSE
                    EXECUTE @result = dbo.UpdateANOTEHR @ID = 15
                    IF @result != 0
                        ROLLBACK
                    ELSE
                        COMMIT
                        SELECT @result 
        END             
END

推荐答案

我强烈建议使用 TRY/CATCH 块和 RAISERROR 而不是 @@ERROR/@result 检查.我有一个博客条目，它展示了如何正确使用事务和 TRY/CATCH 块，包括嵌套事务以仅恢复失败的过程调用工作，以便 calee 可以恢复不同的路径并继续事务，如果感觉像:异常处理和嵌套事务.

I highly recommend using TRY/CATCH blocks and RAISERROR instead of @@ERROR/@result checks. I have a blog entry that shows how to properly use transactions and TRY/CATCH blocks, including nested transactions to revert only the failed procedure call work so that the calee can resume a different path and continue the transaction, if it feels like: Exception Handling and Nested Transactions.

<更新>

您在程序返回模式方面不一致.UpdateTHIS 和 UpdateTHAT 将 0/1 作为返回值返回，而包装器 DoStuff 作为结果集 (SELECT) 返回.这意味着您不能编写调用 DoStuff 的 DoMoreStuff，因为它必须使用 INSERT ... EXEC 来捕获结果，您很快就会发现 INSERT ... EXEC 不能嵌套.我建议改用 RETURN @result 以保持一致性.

You are being inconsistent with regard to procedures return mode. UpdateTHIS and UpdateTHAT return 0/1 as a return value, while the wrapper DoStuff returns as a result set (SELECT). It means you cannot write DoMoreStuff that calls DoStuff because it has to use INSERT ... EXEC to capture the result, and you'll quickly find out that INSERT ... EXEC cannot nest. I recommend using RETURN @result instead, for consistency.

我还有一个不相关的推荐，它只是一种风格元素:我发现很长的 IF... ELSE IF... ELSE IF ... ELSE IF ... 块难以阅读和遵循.我总是发现表达与 DO ... BREAK ... BREAK ... BREAK ... WHILE (FALSE) 一样更容易阅读.T-SQL 没有 DO ... WHILE 结构，因此必须使用 WHILE ... 来代替:

I also have an unrelated commend, which is just an element of style: I find long IF... ELSE IF... ELSE IF ... ELSE IF ... blocks difficult to read and follow. I always found that expressing the same as a DO ... BREAK ... BREAK ... BREAK ... WHILE (FALSE) is easier do read. T-SQL does not have a DO ... WHILE construct, so a WHILE ... has to be used instead:

BEGIN TRANSACTION
WHILE (1=1)
BEGIN
  EXECUTE @result = dbo.UpdateTHIS @ID = 1;         
  IF @result != 0
  BEGIN
     ROLLBACK;
     BREAK;
  END 

  EXECUTE @result = dbo.UpdateTHAT @ID = 21             
  IF @result != 0
  BEGIN
     ROLLBACK;
     BREAK;
  END

  ...

  COMMIT;
  BREAK;
END

同样，这并不重要，因为它只是一种代码格式样式，但如果您同意它会导致代码更易于阅读，那么这是一个建议.

Again, this is no important as is just a code formatting style, but is a suggestion in case you agree that it results in code that is easier to read.

这篇关于存储过程调用多个存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！