如果儲存過程在 TRY / CATCH 內失敗，則未設置輸出參數

在 SQL Server 2008 中（但也在 2014 年）。讓我們考慮一個具有輸出參數的過程。此過程可能會產生錯誤（將在以下範例中出現）。我注意到如果我們在TRY/CATCH塊中呼叫過程，輸出參數的行為是不一樣的。

例子：

create procedure test_output @result varchar(10) output
as
begin
   set @result = 'hello'
   raiserror('This is an error', 16,1)
   set @result = 'error'
end

如果我們以簡單的方式啟動該程序：

declare @res1 varchar(10)
exec test_output @result = @res1 out
print 'Result is: '+ isnull(@res1, 'empty')

我們得到（我很好）：

消息 50000，級別 16，狀態 1，過程 test_output，第 7 行

$$ Batch Start Line 12 $$

這是一個錯誤

結果是：錯誤

如果過程現在在 try/catch 塊中：

declare @res2 varchar(10)
declare @error_message varchar(max)
begin try
   exec test_output @result = @res2 out
end try
begin catch
   set @error_message = error_message()
   raiserror(@error_message, 16,1)
   print 'Result is: '+ isnull(@res2, 'empty')
end catch

我們得到（我很沮喪）：

消息 50000，級別 16，狀態 1，第 28 行

這是一個錯誤

結果是：空

錯誤消息正常，但輸出參數現在為NULL。如果在TRY...CATCH上下文中，執行在 之後立即停止RAISERROR，我會期望輸出值設置為hello。

為什麼會這樣？

您的測試設置有了一個良好的開端，但它缺少一些導致您誤解實際發生的事情的東西。如果您在PRINT儲存過程的開頭、中間和結尾添加語句，那麼額外的輸出將使這裡發生的事情更清楚。例如：

USE [tempdb];
GO
CREATE PROCEDURE test_output
(
 @Result VARCHAR(10) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;

   SET @Result = 'hello';

   PRINT 1;
   RAISERROR('This is an error', 16, 1);

   PRINT 2;
   SET @Result = 'error';

   PRINT 3;
END;
GO

您的第一個測試查詢的輸出是：

1
Msg 50000, Level 16, State 1, Procedure test_output, Line xxxx [Batch Start Line yyyyy]
This is an error
2
3
Result is: error

無論如何，這可能是您所期望的。但是，第二個測試查詢的輸出是：

1
Msg 50000, Level 16, State 1, Line xxxxx
This is an error
Result is: empty

那是相當不同的。我們現在可以看到，在TRY...CATCH構造中，執行在被呼叫時立即RAISERROR停止（即，它變成了批處理中止事件）。另一方面，在構造中RAISERROR未呼叫時不會立即停止執行。TRY...CATCH

但是，正如您在對問題的更新中指出的那樣，這並不能解釋為什麼該OUTPUT參數未設置為hello. 這是由於正常儲存過程行為的意圖是不反映部分執行（由於批處理中止錯誤）。這在以下部落格文章中進行了討論：

TSQL 基礎 II – 參數傳遞語義

含義：即使儲存過程確實執行了將變數設置為 的步驟hello，但RAISERROR現在在TRY...CATCH構造中呼叫時會出現批處理中止錯誤，並且儲存過程在中止時不會反映對參數的任何更改。

這種行為也是以下解釋的核心：

為什麼 TVP 必須是 READONLY，其他類型的參數為什麼不能是 READONLY

引用自：https://dba.stackexchange.com/questions/185416