We are using following error handling pattern in SQL Server stored procedures:
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
but when the below error occurs in the stored procedure the try/catch block didn't work.
Error details: stored Procedure tried to insert a NULL
value into a not null column.
During the execution of the stored procedure, I got following error
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
Msg 3903, Level 16, State 1, Line 30
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Why is the exception not handled? Please help
IF @@ERROR = 0
is redundant. if Sql server does raise an error, the flow should go directly to the catch block. – Zohar PeledCATCH
statement, such as syntax errors e.g. See the documentation for more details (Errors Unaffected by a TRY…CATCH Construct section). – i-one