Replacing RAISERROR with THROW

Question

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.

Answer 1

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 } ] [ ; ]

Answer 2

No, you actually don't need any CATCH at all.

All you need to do is SET XACT_ABORT ON; at the top, and the server will handle rollback. There is no point catching if all you need to is rollback and rethrow.

CREATE PROC my_procName
    @id int
AS

SET XACT_ABORT, NOCOUNT ON;

BEGIN TRAN;

UPDATE [tbl_A]
WHERE ID = @id;

UPDATE [tbl_B]
WHERE fkID = @id;

UPDATE [tbl_C]
WHERE fkID = @id;

COMMIT;