October 9, 2014 at 4:05 am
TomThomson (10/6/2014)
Nice question. But the answer turned up in comments on last weeks raiserror question, so it was a bit easy for anyone who had read that.
I didn`t read that, yet managed to answer the question correctly cause it was in my mind, had read it somwhere sometime 🙂
October 9, 2014 at 4:06 am
Another good question and informative as well, thanx.
October 9, 2014 at 11:31 pm
Jim_K (10/6/2014)
The raiserror documentation says to prefer throw, but it's less capable.
The major advantage of THROW over RAISERROR is true support for "re-raising" errors, which is aligned with the TRY/CATCH constructs in languages like Java and C#.
Consider the case when your insert statement encounters a primary key violation, an error with id 2627. In your stored procedure this PK error transfers control to your catch block. To signal the original caller that a PK error occurred in SQL 2005-2008 R2 we are compelled to write code similar to this:
BEGIN TRY
-- do some stuff...until you encounter a PK error with error ID 2627
END TRY
BEGIN CATCH
DECLARE @err_str VARCHAR(2048),
@err_sev INT,
@err_state INT ;
SELECT @err_str = ERROR_MESSAGE() + N' (Line ' + ISNULL(CAST(ERROR_LINE() AS NVARCHAR(11)), N'(null)') + N')',
@err_sev = ERROR_SEVERITY(),
@err_state = ERROR_STATE() ;
RAISERROR(@err_str, @err_sev, @err_state) ;
END CATCH
When RAISERROR is executed within the catch block the error message id will be reported to the caller as 50000, the default user-defined error message id, because we are not permitted to raise errors with a message id < 13000.
In SQL 2012 and higher we can write code like this:
BEGIN TRY
-- do some stuff...until you encounter a PK error with error ID 2627
END TRY
BEGIN CATCH
-- much cleaner, more effective exception handling code (in my opinion)
IF ERROR_NUMBER() = 50100
BEGIN
-- user-defined error 50100 encountered
-- perform some custom logging or recovery logic here to handle error 50100
END
-- (re)throw the original error to the caller. in the case of error 2627, a
-- PK error, the caller will see that exact error as if there were no catch
-- block in place which was not possible prior to THROW being added to SQL 2012
THROW;
END CATCH
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 30, 2015 at 2:29 pm
+1 great question. Thanks.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply