SQL Server 存储过程中的错误处理 [英] Error Handling in SQL Server Stored Procedures
问题描述
我有一个相当复杂的 SP(逻辑明智)，带有嵌套的 IF BEGIN END ELSE，在这个嵌套逻辑中有多个端点，逻辑失败，我 RAISERROR以及两个导致成功和 SQL 被操作的地方.
如何在 SP 的末尾捕获错误并执行 IF Error Count>0 THEN ROLLBACK
DECLARE @errCount int=0声明 @DataSetCount int=0声明@countCurrent int=0声明@countHistorical int=0IF (isnumeric(@DataSetID)=(0) OR @DataSetID=(0))开始RAISERROR('指定的数据集似乎无效', 5, 1)结尾别的如果(@Destination='C' 或 @Destination='H')开始如果存在(从 tblOpportunityDataSets 中选择 NULL，其中 DataSetID=@DataSetID)开始SET @countCurrent=(SELECT COUNT(1) from tblOptyRecordsCurrent where DataSetID=@DataSetID)SET @countHistorical=(SELECT COUNT(1) from tblOptyRecordsHistorical where DataSetID=@DataSetID)如果@destination='C'开始如果@countCurrent>0开始RAISERROR('当前表中已经存在指定数据集的记录', 5, 1)结束其他如果@countHistorical=0开始RAISERROR('历史表中似乎没有任何记录要为指定的数据集传输', 5, 1)结束其他开始-- 输入传输代码INSERT INTO tblRecordsHistorical( X, Y, Z )从 tblA 中选择 X、Y、Z哪里 x=y-- 检查两个表中的记录数是否匹配SET @countCurrent=(SELECT COUNT(1) from tblOptyRecordsCurrent where DataSetID=@DataSetID)SET @countHistorical=(SELECT COUNT(1) from tblOptyRecordsHistorical where DataSetID=@DataSetID)如果(@countCurrent<>@countHistorical)开始RAISERROR('将记录复制到历史表时出错，源和目标记录计数不匹配', 5, 1)结束其他开始结尾结尾结束其他如果@Destination='H'开始如果@countHistorical>0开始RAISERROR('历史表中已经存在指定数据集的记录', 5, 1)结束其他如果@countCurrent=0开始RAISERROR('历史表中似乎没有任何记录要为指定的数据集传输', 5, 1)结束其他开始RAISERROR('DataSet Found, ready to transfer records to HISTOICAL', 5, 1)-- 输入传输代码插入 tblOptyRecordsCurrent( X, Y, Z )从 tblB 中选择 X、Y、Z哪里 x=y-- 检查两个表中的记录数是否匹配SET @countCurrent=(SELECT COUNT(1) from tblOptyRecordsCurrent where DataSetID=@DataSetID)SET @countHistorical=(SELECT COUNT(1) from tblOptyRecordsHistorical where DataSetID=@DataSetID)结尾结尾结尾别的开始RAISERROR('无法找到您指定的数据集', 5, 1)结尾结尾别的开始RAISERROR('你没有指定一个有效的目的地', 5, 1)结尾请注意，在包含 INSERT INTO 代码的两个部分中，将至少有两个额外的 SQL 操作语句，它们必须全部有效，否则全部失败.
我已经走了
BEGIN TRAN开始尝试-- 声明 1在 Y 处插入 X-- 声明 2从 X 处删除 Y-- 声明 3在 Y 处更新 X犯罪结束尝试开始捕捉回滚传输RAISERROR('将记录复制到当前表时出错.事务已回滚', 5, 1)收尾为了能够做一个rollback，你需要做一个begin transaction开始.
然后您可以commit 或rollback 操作.
http://msdn.microsoft.com/en-us/library/ms174377>
您可能会发现 try/catch 语法更简单
http://msdn.microsoft.com/en-us/library/ms175976(v=sql.90).aspx
I have a fairly complex SP (logic wise) with nested IF BEGIN END ELSE, there are multiple end points in this nested logic where the logic fails and I RAISERROR and two places that result in success and SQL being actioned.
How, at the end of the SP can I trap the errors and do a 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
Note that in the two sections that contain the INSERT INTO code, there will be at least two additional SQL Action Statements, which must all work or they all fail.
EDIT: I've gone with
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
In order to be able to do a rollback, you need to do a begin transaction at the start.
Then you either commit or rollback the actions.
http://msdn.microsoft.com/en-us/library/ms174377
You may find the try/catch syntax easier
http://msdn.microsoft.com/en-us/library/ms175976(v=sql.90).aspx
这篇关于SQL Server 存储过程中的错误处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
