sql-servert-sqlsql-server-2012

Replacing RAISERROR with THROW


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.


Solution

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