TryCatch Example

BEGIN TRAN

--SET NOCOUNT ON
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT

BEGIN TRY
    --Do some work...
END TRY

BEGIN CATCH
    ROLLBACK TRAN;

    SET @ErrorMessage = 'MySp: ' + ERROR_MESSAGE();
    SET @ErrorSeverity = ERROR_SEVERITY();
    SET @ErrorState = ERROR_STATE();

    RAISERROR (
            @ErrorMessage
            ,@ErrorSeverity
            ,@ErrorState
            );

    RETURN;
END CATCH

COMMIT TRAN