Stored PRocedure: @@ERROR problem

  • The way you're doing it is fine. If the error is too severe, the server will rollback that transaction. Then you can check if any transactions are still opened after the execution, and rollback ten if there are.

    Unless someone else knows better (not my domain of expertise).

  • I wondering if you can give a sql sample where you can capture an error??

    I still believe that it will never print your error message.

     

     

     

     

  • Sure :

    GO

    CREATE PROCEDURE dbo.Test @Dividor as int

    AS

    SET NOCOUNT ON

    Declare @Er as int

    begin tran

    Select 10 / @Dividor

    Set @Er = @@Error

    if @Er 0

    begin

    print 'There''s an error'

    rollback tran

    end

    else

    begin

    print 'No problem -- commiting tran'

    commit tran

    end

    SET NOCOUNT OFF

    GO

    exec dbo.test 10

    /*

    1

    No problem -- commiting tran

    */

    exec dbo.test 0

    /*

    Serveur : Msg 8134, Niveau 16, État 1, Procédure Test, Ligne 6

    Division par zéro.

    There's an error

    */

    GO

    DROP PROCEDURE test

    GO

  • I know the method I use can't be easily applied to most systems already developed but this is what I do.

    Since the default return value from a sproc is 0, I code all my procedures to Return 1 on a condition of success.  If the calling procedure/client doesn't get a Return of 1 assume an error has occurred.  Of course I still do all the normal error checking and Raiserror stuff after each trip to the database.

    Hope that helps someone.

    -ron

Viewing 4 posts - 16 through 18 (of 18 total)

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