CODEWITHSUNDEEP
sql-serverexceptionsql-server-2008-r2
scarpacci
scarpacci

Reputation: 9194

Sql Server Custom Exceptions

So in Sql Server it appears that I cannot have specific steps within my catch branch like I can in PL/SQL (I could add IF / ELSE checks).

So in PL/SQL I would have something like this

DECLARE

  MY_EXCEPTION EXCEPTION;

BEGIN

  //My Error is Raised

EXCEPTION
  WHEN MY_EXCEPTION THEN
   //Perform actions

END

How are others handling this? Is there a more elegant solution with the TRY / CATCH than using IF Statements to look at the errors and perform operations?

Thanks,

S

Upvotes: 0

Views: 2618

Answers (2)

bjorsig
bjorsig

Reputation: 1107

In the most recent SQL Servers you can use try catch blocks like:

BEGIN try

the code

END try

BEGIN catch

do whatever you need to do in case of exception

END catch

In older versions you are stuck with

IF @@ERROR > 0 THEN BEGIN

do you stuff

END IF

Upvotes: 0

anon
anon

Reputation:

What kind of operations? Do you mean you want to declare elsewhere that for exception A it gets logged and exception B just gets ignored, and have your catch block inherit those actions? SQL Server 2012 adds THROW so that you can do other things (log, send e-mail, whatever) and then essentially re-throw the error that triggered the catch in the first place - but there is no way to define error handling as such centrally unless you pass the error number, severity etc. to a stored procedure (then the logic could be done in the procedure instead of in the CATCH block). Quick example:

CREATE PROCEDURE dbo.CustomErrorHandler
    @ErrorNumber INT,
    @ProcID INT
AS
BEGIN
    IF @ErrorNumber = 8134
    BEGIN
        PRINT 'Oh, it was just divide by 0 in '
            + COALESCE(OBJECT_NAME(@ProcID), 'ad hoc');
        RETURN;
    END

    IF @ErrorNumber = 208
    BEGIN
        PRINT 'Invalid object access!';
        -- send e-mail about invalid object access
        RETURN;
    END

    /* other custom handling for other exceptions */

    RAISERROR('Unhandled exception I guess?', 11, 1);
END
GO

Then you could play with various exceptions (well, ones that pass the parsing phase, at least):

BEGIN TRY
    SELECT 1/0; --8134
    --EXEC('SELECT * FROM splunge;'); --208
END TRY
BEGIN CATCH
    DECLARE 
        @e INT = ERROR_NUMBER(),
        @p INT = @@PROCID;

    EXEC dbo.CustomErrorHandler @e, @p;
END CATCH

For the bible on error handling see Erland's articles http://www.sommarskog.se/error-handling-I.html, http://www.sommarskog.se/error-handling-II.html and http://www.sommarskog.se/error_handling_2005.html

Upvotes: 3

Related Questions