Post reply

Error should not be return....

  • November 10, 2005 at 1:50 am

    #89891

    Hi,

    I have the code snipptes as below in Store Procedure:

    set nocount on

    select 2/0

    if @@error <> 0

    BEGIN

     SELECT 'Error'

    END

    When I run the SP through Application the system Error along with the "Error" is return. I don't want it to be return. I want only Select 'Error' to be return from the IF block.

    Please help... 

    Kindest Regards,

    Vishal Prajapati

    DBA at Extentia Infromation Technology

  • November 10, 2005 at 5:16 am

    #603195

    I have no clue, recently someone asked the same for warnings.

    You can raise a custom error with RAISERROR but the original error will be sent also (I presume).

    Does the application has a data access layer seperated from the business/UI layer where you can translate those errors?

  • November 10, 2005 at 10:22 am

    #603284

    Select 2/0

    Server: Msg 8134, Level 16, State 1, Line 2

    Divide by zero error encountered.

    Note the Level 16 - too high to be trappable.

  • November 14, 2005 at 9:12 am

    #603701

    I don't understand what the purpose of this may be...but here's a possible workaround...

    <pre class="code"

    SET ANSI_WARNINGS OFF

    SET ARITHABORT OFF

    IF 2/0 IS NULL

    BEGIN

    SELECT 'Error'

    END

    SET ANSI_WARNINGS ON

    SET ARITHABORT ON







    **ASCII stupid question, get a stupid ANSI !!!**

  • November 15, 2005 at 2:32 pm

    #604040

    In testing I noticed that as you stated you can't trap the error but at the same time the stored procedure does NOT abort.  If you don't test for the error and take action the procedure continues on and will commit!

    That's a gotcha!!

  • November 16, 2005 at 2:22 am

    #604112

    Hi,

    Vishal again...

    Thankz to all for your reply.

    But the solution which you have given is specific to the arithmetic error. I want to have the general solution. If any type of system error is generated it should not be thrown to application. I want to manage it through SP itself.

     

    Kindest Regards,

    Vishal Prajapati

    DBA at Extentia Infromation Technology

  • November 17, 2005 at 9:29 am

    #604455

    The error handling in sql server 2000 is not very good and there may not be a reasonable way around your problem.  The only other thing I can think of is to look at sql server 2005 where the error handling is supposed to much improved by the use of Try/Catch code blocks.

  • November 17, 2005 at 9:47 am

    #604465

    Yikes - maybe it's an epidemic!

    I've seen 2 similar posts in as many days with the same requirement! Vishal - one of these links has a couple of links posted by Frank Kalis on error handling - maybe you'll get some pointers from there...

    error description in stored procedure

    retrieving SQL error message







    **ASCII stupid question, get a stupid ANSI !!!**

Viewing 8 posts - 1 through 7 (of 7 total)

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