我有嵌套一个相当复杂的SP（逻辑明智） IF BEGIN END ELSE ，有多个端点在此嵌套逻辑，其中逻辑失败，我RAISERROR ，并导致成功和SQL正在付诸行动两个地方。
怎么样，在SP结束可以捕获错误，并做了IF Error Count>0 THEN ROLLBACK
DECLARE @errCount int=0
DECLARE @DataSetCount int=0
DECLARE @countCurrent int=0
DECLARE @countHistorical int=0
IF (isnumeric(@DataSetID)=(0) OR @DataSetID=(0))
BEGIN
RAISERROR('The DataSet specfied does not appear to be valid', 5, 1)
END
ELSE
IF (@Destination='C' OR @Destination='H')
BEGIN
if Exists (SELECT NULL from tblOpportunityDataSets where DataSetID=@DataSetID)
BEGIN
SET @countCurrent=(SELECT COUNT(1) from tblOptyRecordsCurrent where DataSetID=@DataSetID)
SET @countHistorical=(SELECT COUNT(1) from tblOptyRecordsHistorical where DataSetID=@DataSetID)
IF @destination='C'
BEGIN
if @countCurrent>0
BEGIN
RAISERROR('There are already existing records in the Current Tables for the specified DataSet', 5, 1)
END ELSE
if @countHistorical=0
BEGIN
RAISERROR('There do not appear to be any records in the Historical Tables to transfer for the specified Dataset', 5, 1)
END ELSE
BEGIN
-- ENTER TRANSFER CODE
INSERT INTO tblRecordsHistorical
( X, Y, Z )
SELECT X, Y, Z FROM tblA
WHERE x=y
-- Check that record count in both tables match
SET @countCurrent=(SELECT COUNT(1) from tblOptyRecordsCurrent where DataSetID=@DataSetID)
SET @countHistorical=(SELECT COUNT(1) from tblOptyRecordsHistorical where DataSetID=@DataSetID)
IF (@countCurrent<>@countHistorical)
BEGIN
RAISERROR('There was an error whilst copying the records into the Historical Tables, Source and Destination Record Count do not match', 5, 1)
END ELSE
BEGIN
END
END
END ELSE
IF @Destination='H'
BEGIN
if @countHistorical>0
BEGIN
RAISERROR('There are already existing records in the Historical Tables for the specified DataSet', 5, 1)
END ELSE
if @countCurrent=0
BEGIN
RAISERROR('There do not appear to be any records in the Historical Tables to transfer for the specified Dataset', 5, 1)
END ELSE
BEGIN
RAISERROR('DataSet Found, ready to transfer records to HISTORICAL', 5, 1)
-- ENTER TRANSFER CODE
INSERT INTO tblOptyRecordsCurrent
( X, Y, Z )
SELECT X, Y, Z FROM tblB
WHERE x=y
-- Check that record count in both tables match
SET @countCurrent=(SELECT COUNT(1) from tblOptyRecordsCurrent where DataSetID=@DataSetID)
SET @countHistorical=(SELECT COUNT(1) from tblOptyRecordsHistorical where DataSetID=@DataSetID)
END
END
END
ELSE
BEGIN
RAISERROR('The DataSet you have specified cannot be found', 5, 1)
END
END
ELSE
BEGIN
RAISERROR('You have not specified a valid Destination', 5, 1)
END
需要注意的是在包含INSERT INTO代码的两节中，将有至少两个额外的SQL操作语句，必须所有的工作或全部失败。
编辑：我已经用了
BEGIN TRAN
BEGIN TRY
-- STATEMENT 1
INSERT INTO X WHERE Y
-- STATEMENT 2
DELETE FROM X WHERE Y
-- STATEMENT 3
UPDATE X WHERE Y
COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRAN
RAISERROR('There was an error whilst copying the records into the Current Tables. The Transaction has been rolled back', 5, 1)
END CATCH
