error handling -- @@ERROR

error handling -- @@ERROR

Post by Blinkie Billi » Fri, 15 Feb 2002 19:31:05



Hi!

I want to put error handling into stored procedures.


-- Create the procedure.

AS

-- Execute the INSERT statement.
INSERT INTO authors
(au_id,  au_lname, au_fname, phone, address,

-- Test the error value.

BEGIN
   -- Return 99 to the calling program to indicate failure.
   PRINT "An error occurred loading the new author information"
   RETURN(99)
END
ELSE
BEGIN
   -- Return 0 to the calling program to indicate success.
   PRINT "The new author information has been loaded"
   RETURN(0)
END
GO

But it's not working, because when the error occurs the server throws an
exception so the code does not reach the error handling section....

Are there any settings to switch this out or something else?

Thanks in advance,
                                 B.B.

 
 
 

error handling -- @@ERROR

Post by Andrew Joh » Fri, 15 Feb 2002 21:13:44


Blinkie Billie ?

On behalf of all Australians I would ask you not to
steal a handle of such national importance.

In answer to your question.
No. There is no try .. catch or structured exception handling.
The best you can do is test for every possible thing that can break
your code in the lines prior to running it

eg  IF NOT EXISTS   already only then do the insert

or




logic is also useful - preventing subsequent code from aborting the stored
proc so you can return gracefully.

TRANSACTIONS are useful too, although if you nest them you must
commit all the inside transactions, even under error conditions,
and ROLLBACK only the outermost.

The most reliable way of coding I have found is to catch all the data type
errors
with the above kind of checks, and to do the real exception handling at the
client.

Regards
 AJ


> Hi!

> I want to put error handling into stored procedures.


> -- Create the procedure.
> CREATE PROCEDURE add_author





> AS

> -- Execute the INSERT statement.
> INSERT INTO authors
> (au_id,  au_lname, au_fname, phone, address,
> city, state, zip, contract) values


> -- Test the error value.

> BEGIN
>    -- Return 99 to the calling program to indicate failure.
>    PRINT "An error occurred loading the new author information"
>    RETURN(99)
> END
> ELSE
> BEGIN
>    -- Return 0 to the calling program to indicate success.
>    PRINT "The new author information has been loaded"
>    RETURN(0)
> END
> GO

> But it's not working, because when the error occurs the server throws an
> exception so the code does not reach the error handling section....

> Are there any settings to switch this out or something else?

> Thanks in advance,
>                                  B.B.


 
 
 

error handling -- @@ERROR

Post by Erland Sommarsk » Fri, 15 Feb 2002 20:44:12



>-- Create the procedure.
>CREATE PROCEDURE add_author





>AS

>-- Execute the INSERT statement.
>INSERT INTO authors
>(au_id,  au_lname, au_fname, phone, address,
>city, state, zip, contract) values


>-- Test the error value.

>BEGIN
>   -- Return 99 to the calling program to indicate failure.
>   PRINT "An error occurred loading the new author information"
>   RETURN(99)
>END

There is one problem here. The code is working alright, but say you would


  BEGIN
     -- Return 99 to the calling program to indicate failure.
    PRINT "An error occurred loading the new author information"

  END



local variable, to advoid mishaps.

Quote:>But it's not working, because when the error occurs the server throws an
>exception so the code does not reach the error handling section....

>Are there any settings to switch this out or something else?

No. SQL Server's error handling is unfortunately poor. Some errors causes
the entire batch to abort, so you cannot catch the errors in the stored
procedure. Other errors causes only the current procedure to abort, but
executions continues in the calling procedure. And some errors only aborts

As for which error that causes which, there is not really any consistency.
A constraint violation may sound fatal enough to cause * of the
batch, but nope, excution continues. A conversion error on the other hand
aborts the batch.
--

 
 
 

error handling -- @@ERROR

Post by Greg Linwoo » Fri, 15 Feb 2002 21:19:11


Hi Blinkie. (gday, mate!)

Unfortunately, when certain types of runtime errors occur, code does not
proceed to execute as documented in BOL.

One example of this is where the table named in a DML statement (eg INSERT)
does not exist.

If you have copied this stored proc to a database without the table, you
should experience the behaviour you've described.

Do not bother with transactions in this case, because this problem only
becomes much worse when they're introduced as the transactions become

checking code never executes, therefore neither does the commit / rollback,
leaving the transaction orphaned and the locked resources locked..

This is an important issue with T-SQL error handling which you need to
carefully plan around. If you can GUARANTEE the existence of the tables in
your DML statements, then you have less to worry about. If not, then you
cannot guarantee your error handling code will work - and this also means
that your transactions may become orphaned.

Please refer to my post entitled "SET XACT_ABORT just doesn't work..." on
January 25th if you're interested in more details on this error handling
problem.

HTH

Cheers,
Greg Linwood


> Blinkie Billie ?

> On behalf of all Australians I would ask you not to
> steal a handle of such national importance.

> In answer to your question.
> No. There is no try .. catch or structured exception handling.
> The best you can do is test for every possible thing that can break
> your code in the lines prior to running it

> eg  IF NOT EXISTS   already only then do the insert

> or




> logic is also useful - preventing subsequent code from aborting the stored
> proc so you can return gracefully.

> TRANSACTIONS are useful too, although if you nest them you must
> commit all the inside transactions, even under error conditions,
> and ROLLBACK only the outermost.

> The most reliable way of coding I have found is to catch all the data type
> errors
> with the above kind of checks, and to do the real exception handling at
the
> client.

> Regards
>  AJ



> > Hi!

> > I want to put error handling into stored procedures.


> > -- Create the procedure.
> > CREATE PROCEDURE add_author





> > AS

> > -- Execute the INSERT statement.
> > INSERT INTO authors
> > (au_id,  au_lname, au_fname, phone, address,
> > city, state, zip, contract) values


> > -- Test the error value.

> > BEGIN
> >    -- Return 99 to the calling program to indicate failure.
> >    PRINT "An error occurred loading the new author information"
> >    RETURN(99)
> > END
> > ELSE
> > BEGIN
> >    -- Return 0 to the calling program to indicate success.
> >    PRINT "The new author information has been loaded"
> >    RETURN(0)
> > END
> > GO

> > But it's not working, because when the error occurs the server throws an
> > exception so the code does not reach the error handling section....

> > Are there any settings to switch this out or something else?

> > Thanks in advance,
> >                                  B.B.