事務的一半完成，另一半失敗？

我在 SO、MSDN 和其他各種來源上進行了一些搜尋，並且發現了很多關於事務的問題，但似乎沒有一個是我正在處理的問題。

誠然，我仍然是一名初級 DBA，儘管我確實了解 ACID 和事務的概念。我正在編寫一個 SQL 腳本：

1. 將一行添加到 2 個不同的表中，前提是這些值尚不存在
2. 如果存在則刪除列
3. 如果列不存在則添加
4. 更改 2 個生成動態 SQL 的儲存過程以建構報告查詢

上述所有操作都是單獨工作的，並且在（我認為是）單個事務中完成時它們也可以一起工作。我試圖理解的是，當事務的另一部分失敗時，事務的“部分”如何完成（並且可能是送出）。

為簡潔起見，假設我有名為“ProcessReport”和“RetrieveReport”的工作程序。這兩個過程中的動態 SQL 都以“SELECT”開頭，然後從那裡建構。測試案例：

CREATE TABLE ReportTable (FirstName VARCHAR(100), LastName VARCHAR(100), OrderId INT);
GO
CREATE PROCEDURE ProcessReport
AS
BEGIN
 DECLARE @SQL VARCHAR(4000)

 -- start creating dynamic sql   
 SET @SQL = 'SELECT FirstName, LastName, '
 --create rest of dynamic SQL here...

 INSERT INTO ReportTable (FirstName, LastName) EXEC(@SQL)
END
GO 

CREATE PROCEDURE RetrieveReport
AS
BEGIN
 DECLARE @SQL VARCHAR(4000)

 -- start creating dynamic sql   
 SET @SQL = 'SELECT FirstName, LastName '
 --create rest of dynamic SQL here...

 SET @SQL = @SQL + ' FROM ReportTable '
 EXEC (@SQL)
END
GO   

既然程序已經存在，這就是讓我感到困惑的地方：當我故意為第一個程序輸入錯誤的名稱時ALTER PROCEDURE，我對第二個程序所做的編輯就完成了。以下是我編寫的交易的簡短版本：

BEGIN TRANSACTION 
GO 

ALTER PROCEDURE ProcessReport2  -- this procedure does not exist, causing an error
AS
BEGIN
 DECLARE @SQL VARCHAR(4000)

 -- start creating dynamic sql   
 SET @SQL = 'SELECT FirstName, LastName, '
 --create rest of dynamic SQL here...

 INSERT INTO ReportTable(FirstName, LastName) EXEC(@SQL)
END
GO
ALTER PROCEDURE RetrieveReport
AS
BEGIN
 DECLARE @SQL VARCHAR(4000)

 -- start creating dynamic sql   
 SET @SQL = 'SELECT DISTINCT FirstName, LastName '  --DISTINCT added
 --create rest of dynamic SQL here...

 SET @SQL = @SQL + ' FROM ReportTable '
 EXEC (@SQL)

END
GO 

IF @@ERROR = 0
 COMMIT TRANSACTION;
ELSE
 ROLLBACK TRANSACTION;

執行上述語句時，出現以下錯誤：

消息 208，級別 16，狀態 6，過程 ProcessReport2，第 2 行無效的對象名稱“ProcessReport2”。

消息 3902，級別 16，狀態 1，第 3 行 COMMIT TRANSACTION 請求沒有相應的 BEGIN TRANSACTION。

關於 ACID 和事務，我的理解是，BEGIN TRANSACTION 和 COMMIT TRANSACTION 之間的所有事情要麼完成，要麼什麼都不做。在 SSMS 中，當我在 RetrieveReport 過程上點擊“修改”時，我現在看到以“SELECT DISTINCT…”開頭的動態 SQL

誰能告訴我這裡哪裡出錯了？是不是我的東西

IF @@ERROR = 0
COMMIT TRANSACTION;

ELSE
ROLLBACK TRANSACTION;

我的意圖是“如果沒有錯誤，送出整個事情，如果有錯誤，不要做任何事情。”

我正在使用 SQL Server 2012 企業版。

任何幫助和/或見解將不勝感激。

@@ERROR在每條語句後重置。@@ERROR在您成功更改第二個過程後，您嘗試更改第一個過程的錯誤不再可檢測到。這是一個更簡單的複制：

SELECT 1/0;
GO
SELECT @@ERROR; -- 8134

但是，如果我在兩者之間放置一個成功的聲明：

SELECT 1/0;
GO
SELECT 1/1;
GO
SELECT @@ERROR; -- 0

在您的情況下，您正在這樣做：

ALTER dbo.p1 ... -- fails
GO
-- @@ERROR here would be <> 0, but you're not checking it here!

ALTER dbo.p2 ... -- succeeds
GO
-- You're checking @@ERROR here, but success is 0

您目前的邏輯將所有內容回滾的唯一方法是最後一次 ALTER失敗。您需要@@ERROR在每個 ALTER. 這是一個例子。

USE tempdb;
GO
CREATE TABLE #x(err INT);
GO

BEGIN TRANSACTION;
GO

CREATE PROCEDURE dbo.foo -- fails
AS 
 SELECT foo FROM sys.objects;
GO
IF @@ERROR <> 0 
 INSERT #x(err) SELECT 1;
GO

CREATE PROCEDURE dbo.blat -- succeeds, but will get rolled back
AS 
 SELECT 1;
GO
IF @@ERROR <> 0 
 INSERT #x(err) SELECT 1;
GO

IF EXISTS (SELECT 1 FROM #x)
 ROLLBACK TRANSACTION;
ELSE
 COMMIT TRANSACTION;

DROP TABLE #x;

正如 Aaron 所解釋的，@@ERROR需要在每個ALTER PROCEDURE. 無法避免這種情況，但對於使用事務，如果您願意接受有關建議，您可以用不同的方法替換它。

您可以操縱SET NOEXEC設置來獲得與您使用事務相同的結果：兩個過程都已更改或都保持不變。這是如何：

SET NOEXEC OFF  -- usually the default, but just in case
GO

ALTER PROCEDURE dbo.proc1
AS
 . . .
GO

IF @@ERROR <> 0  -- if an error occurred altering dbo.proc1
 SET NOEXEC ON  -- then skip altering the following one(s)
;
GO

ALTER PROCEDURE dbo.proc2
AS
 . . .
GO

IF @@ERROR <> 0  -- apply the same after each procedure that you want
 SET NOEXEC ON  -- to be either altered or skipped as a single batch
;
GO

. . .

ALTER PROCEDURE dbo.procN
AS
 . . .
GO

IF @@ERROR <> 0  -- strictly, no need to use it after the last one
 SET NOEXEC ON  -- but it is no harm if you do
;
GO

SET NOEXEC OFF  -- just reset to proceed with the rest of the script normally
GO
. . .

SET NOEXEC ON導致 SQL Server 停止執行後續語句，直到腳本結束或SET NOEXEC OFF遇到直到，後者將執行重新設置為打開。

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