In my procedures historically, I've always caught an exception, then raised it after a ROLLBACK
. I see in MSDN that the recommended approach (for SQL2012+) is to THROW
.
Based on this example procedure:
CREATE PROC my_procName
@id int
AS
BEGIN TRY
BEGIN TRAN
UPDATE [tbl_A] WHERE (ID=@id);
UPDATE [tbl_B] WHERE (fkID=@id);
UPDATE [tbl_C] WHERE (fkID=@id);
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
DECLARE @ErrMsg nvarchar(4000)
DECLARE @ErrSeverity int
SET @ErrMsg = ERROR_MESSAGE()
SET @ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
GO
Is this the correct way to throw the exception, while preserving the ROLLBACK
?
CREATE PROC my_procName
@id int
AS
BEGIN TRY
BEGIN TRAN
UPDATE [tbl_A] WHERE (ID=@id);
UPDATE [tbl_B] WHERE (fkID=@id);
UPDATE [tbl_C] WHERE (fkID=@id);
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
THROW
END CATCH
GO
I have already looked at MSDN, Google, and this site for examples, but none include the ROLLBACK
, so this is just a quick question to make absolutely sure.
Just for the record statement before THROW statement should be terminated by semicolon. But generally your approach is correct - THROW ends the batch therefore must be the last statement you want to execute in your catch block. Optionally you can use THROW with parameters:
THROW [ { error_number | @local_variable },
{ message | @local_variable },
{ state | @local_variable } ]
[ ; ]