我们在SQL Server存储过程中使用以下错误处理模式:
ALTER PROCEDURE [dbo].[USP_Districtdata_Import]
@DistrictData DistrictData Readonly
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN
--Insert the new records into BudgetDistrict Table.
INSERT INTO [dbo].[BudgetDistrict]
(
DistrictID,
[Year],
Season,
BudgetStateID,
ProjectedReturnCountIsCalc,
RowStatus,
CreatedBy,
CreatedDate,
LastModifiedBy,
LastModifiedDate,
EnableBudgetLock
)
SELECT
DISTINCT list.[District Id],list.[Year],list.[Season],1,0,'A',@CreatedBy,@Updtime,@CreatedBy,@Updtime,0
FROM @DistrictData liston]
AND bud.RowStatus = 'A'
)
LEFT OUTER JOIN [dbo].[BudgetDistrict] bud
ON (bud.DistrictID = list.[District Id]
AND bud.[Year] = list.[Year]
AND bud.[Season] = list.[Seas
WHERE bud.DistrictID IS NULL
--Update the existing pending budgets
UPDATE wk
SET wk.Budget = list.[Budget],
wk.BudgetAdjusted = list.[Budget],
wk.ProjectedReturnCount = list.[ProjectedReturn Count],
wk.CreatedBy = @CreatedBy,
wk.CreatedDate = @Updtime,
wk.LastModifiedBy = @CreatedBy,
wk.LastModifiedDate = @Updtime
FROM @DistrictData list
INNER JOIN [dbo].[BudgetDistrict] bud
ON (bud.DistrictID = list.[District Id]
AND bud.[Year] = list.[Year]
AND bud.[Season] = list.[Season])
INNER JOIN [dbo].[BudgetDistrictWeekly] wk
ON (wk.NationalBudgetID = bud.BudgetDistrictID
AND wk.[WeekDate] = list.[Week])
WHERE bud.RowStatus = 'A'
AND wk.RowStatus = 'A'
AND bud.BudgetStateID = 1
--Insert the new budgets
INSERT INTO [dbo].[BudgetDistrictWeekly]
(
WeekDate,
Budget,
BudgetAdjusted,
RowStatus,
CreatedBy,
CreatedDate,
LastModifiedBy,
LastModifiedDate,
ProjectedReturnCount
)
SELECT LIST.[Week],list.[Budget],list.[Budget],'A',@CreatedBy,@Updtime,@CreatedBy,@Updtime,[ProjectedReturn Count]
FROM @DistrictData list
LEFT JOIN [dbo].[BudgetDistrict] bud
ON (bud.DistrictID = list.[District Id]
AND bud.[Year] = list.[year]
AND bud.[Season] = list.Season
AND bud.RowStatus = 'A')
WHERE bud.DistrictID IS NULL
IF @@ERROR = 0
BEGIN
COMMIT TRAN;
END
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
ROLLBACK TRAN;
END CATCH
SET NOCOUNT OFF;
END
但是当存储过程中发生以下错误时,try / catch块不起作用 .
错误详细信息:存储过程尝试将 NULL
值插入非空列 .
在执行存储过程期间,出现以下错误
EXECUTE之后的事务计数表示BEGIN和COMMIT语句的数量不匹配 . 先前计数= 1,当前计数= 0.消息3903,级别16,状态1,行30 ROLLBACK TRANSACTION请求没有相应的BEGIN TRANSACTION .
为什么不处理异常?请帮忙
1 回答
根据documentation(不受TRY ... CATCH构造部分影响的错误)CATCH语句不会捕获一些错误 .
尤其:
编译阻止批处理运行的错误,例如语法错误 .
语句级重新编译期间发生的错误,例如由于延迟名称解析而在编译后发生的对象名称解析错误 .
相当典型的情况是存储过程访问在创建存储过程后删除的表列(或访问对象) .
请参阅以下示例:
上面的代码产生
如您所见,尽管严重性级别为16,但未捕获错误且未打印消息
Error happened
.