Professional Web Applications Themes

Trapping errors from stored procedure calls - Microsoft SQL / MS SQL Server

We often use code in stored procedures for premature exit, such as SET returnerror = ERROR IF ( returnerror <> 0 ) BEGIN RAISERROR( 'Whoopsie in sub-module 3 - error %d', 16, 1, returnerror) RETURN returnerror END Another program detects that this has happened as follows: EXEC returnerror = StoredProcedure parameter1, parameter2 IF ( returnerror <> 0 ) BEGIN RAISERROR( 'Whoopsie in StoredProcedure - error %d', 16, 1, returnerror) RETURN returnerror END I am wondering if this instead should be, for instance, EXEC return = StoredProcedure parameter1, parameter2 SET returnerror = ERROR IF ( returnerror <> 0 ) BEGIN RAISERROR( ...

  1. #1

    Default Trapping errors from stored procedure calls

    We often use code in stored procedures for premature exit, such as

    SET returnerror = ERROR
    IF ( returnerror <> 0 )
    BEGIN
    RAISERROR( 'Whoopsie in sub-module 3 - error %d',
    16, 1, returnerror)
    RETURN returnerror
    END


    Another program detects that this has happened as follows:

    EXEC returnerror = StoredProcedure parameter1, parameter2
    IF ( returnerror <> 0 )
    BEGIN
    RAISERROR( 'Whoopsie in StoredProcedure - error %d',
    16, 1, returnerror)
    RETURN returnerror
    END


    I am wondering if this instead should be, for instance,


    EXEC return = StoredProcedure parameter1, parameter2
    SET returnerror = ERROR
    IF ( returnerror <> 0 )
    BEGIN
    RAISERROR( 'Whoopsie invoking StoredProcedure - error %d',
    16, 1, returnerror)
    RETURN returnerror
    END
    IF ( return <> 0 )
    BEGIN
    RAISERROR( 'Whoopsie during StoredProcedure - error %d',
    16, 1, return)
    RETURN return
    END


    - i.e., is our current style liable to miss some errors?

    (For instance, invalid parameters, before StoredProcedure even runs?)
    Robert Carnegie Guest

  2. #2

    Default Re: Trapping errors from stored procedure calls

    Hi

    Your second example checks errors returned by the invocation of the
    procedure and the value returned by the procedure, therefore that is the
    method to use.

    You may get away with a single IF statement if you can use a construct like:
    EXEC return = StoredProcedure parameter1, parameter2
    SET return = ABS(ERROR) + ABS(return)
    ....

    John

    "Robert Carnegie" <rja.carnegieexcite.com> wrote in message
    news:f3f18bc0.0307070305.778c70b7posting.google.c om...
    > We often use code in stored procedures for premature exit, such as
    >
    > SET returnerror = ERROR
    > IF ( returnerror <> 0 )
    > BEGIN
    > RAISERROR( 'Whoopsie in sub-module 3 - error %d',
    > 16, 1, returnerror)
    > RETURN returnerror
    > END
    >
    >
    > Another program detects that this has happened as follows:
    >
    > EXEC returnerror = StoredProcedure parameter1, parameter2
    > IF ( returnerror <> 0 )
    > BEGIN
    > RAISERROR( 'Whoopsie in StoredProcedure - error %d',
    > 16, 1, returnerror)
    > RETURN returnerror
    > END
    >
    >
    > I am wondering if this instead should be, for instance,
    >
    >
    > EXEC return = StoredProcedure parameter1, parameter2
    > SET returnerror = ERROR
    > IF ( returnerror <> 0 )
    > BEGIN
    > RAISERROR( 'Whoopsie invoking StoredProcedure - error %d',
    > 16, 1, returnerror)
    > RETURN returnerror
    > END
    > IF ( return <> 0 )
    > BEGIN
    > RAISERROR( 'Whoopsie during StoredProcedure - error %d',
    > 16, 1, return)
    > RETURN return
    > END
    >
    >
    > - i.e., is our current style liable to miss some errors?
    >
    > (For instance, invalid parameters, before StoredProcedure even runs?)

    John Bell Guest

Similar Threads

  1. Trapping errors
    By KenJohnson in forum Macromedia Flex General Discussion
    Replies: 2
    Last Post: May 15th, 01:15 AM
  2. TRapping Errors from a Java Component
    By mherel in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: June 10th, 03:27 PM
  3. Help, MVS DB2 Remote Stored Procedure calls
    By PWSmith in forum IBM DB2
    Replies: 2
    Last Post: August 22nd, 05:59 AM
  4. trapping compile time errors
    By Gerard A.W. Vreeswijk in forum Ruby
    Replies: 5
    Last Post: July 7th, 12:05 PM
  5. Creating a trigger that calls a stored procedure
    By Elie in forum Oracle Server
    Replies: 2
    Last Post: December 29th, 06:13 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139