THROW vs RAISERROR with bubbling up

Question

THROW vs RAISERROR with bubbling up

Mark Eckeard

Hall of Fame

Points: 3462
More actions
August 5, 2020 at 2:08 pm

#3777497

Hi all,
I know THROW is newer and recommended but I'm wondering if one bubbles up and the other doesn't. Specifically, let's say I have nested Try/Catch's and the error happens in the second one. Which bubbles up? Or does the Catch handle that?
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

Begin try
select 1/1
begin try
select 1/0
end try
begin catch
;Throw 51000, 'inner catch', 127
end catch
end try
begin catch
;Throw 51000, 'outer catch', 127
end catch

This reply was modified 4 years, 3 months ago by Mike01.

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ratbak Ten Centuries Points: 1358 More actions · Answer 2

Not the original question, but bubbling up unchanged error is another advantage of THROW. If you want to bubble up the error, unchanged, you can simply call THROW, and it will re-throw the original error -- error number, line number, and message.

With RAISERROR, you can capture and return the original error message, but it will return 50000 as the messageid (unless you use a stored messageid).

Converting Mike01's script to pass through original error:

begin try
    select 1/1
    begin try
        select 1/0
    end try
    begin catch
        throw;
    end catch
end try
begin catch
    throw;
end catch

This reply was modified 4 years, 3 months ago by ratbak.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Throw bubbles up, but only if you choose to do so ... you have control

BEGIN TRY
    BEGIN TRY
        BEGIN TRY
            SELECT 1 / 0;
        END TRY
        BEGIN CATCH
            THROW 52000, 'Non-Bubbler', 1;
        END CATCH;
    END TRY
    BEGIN CATCH
        PRINT 'Not bubbling';
    END CATCH;
END TRY
BEGIN CATCH
    THROW;
END CATCH;

BEGIN TRY
    BEGIN TRY
        BEGIN TRY
            SELECT 1 / 0;
        END TRY
        BEGIN CATCH
            THROW 52000, 'Here''s a Bubbler', 1;
        END CATCH;
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH;
END TRY
BEGIN CATCH
    THROW;
END CATCH;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Mike01 SSChampion Points: 11395 More actions · Answer 4

THROW 52000, 'Here''s a Bubbler', 1;

Phil, In MA we call that a Bubbla

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/