Professional Web Applications Themes

error not trapping errors? - Microsoft SQL / MS SQL Server

Hi, I have a stored procedure (SQL Server 2000) that has the following form. CREATE PROCEDURE ins AS INSERT INTO TABLE A VALUES (...) IF error = 2627 PRINT 'OOPS' GO Now when I insert duplicate recs, I should just get the message OOPS. This does not seem to happen, though. When I execute the procedure in Query yzer, I keep getting the entire SQL Server message saying that a unique constraint violation occured followed by the word OOPS. WHy is this happening? SHould'nt my result just be OOPS and not the entire error string considering I'm trapping the error. ...

  1. #1

    Default error not trapping errors?

    Hi,

    I have a stored procedure (SQL Server 2000) that has the following
    form.

    CREATE PROCEDURE ins
    AS
    INSERT INTO TABLE A VALUES (...)
    IF error = 2627
    PRINT 'OOPS'
    GO

    Now when I insert duplicate recs, I should just get the message OOPS.
    This does not seem to happen, though.

    When I execute the procedure in Query yzer, I keep getting
    the entire SQL Server message saying that a unique constraint violation
    occured followed by the word OOPS. WHy is this happening? SHould'nt
    my result just be OOPS and not the entire error string considering I'm
    trapping the error.

    Thanks

    DrD
    Dr Guest

  2. #2

    Default Re: error not trapping errors?

    > occured followed by the word OOPS. WHy is this happening? SHould'nt 

    No. You will get an error followed by your message. You can't trap the error
    in TSQL. Some options:

    1. Change your INSERT to an INSERT SELECT like this:

    INSERT INTO A (keycol, colx)
    SELECT 123, 'Some Data'
    WHERE NOT EXISTS
    (SELECT *
    FROM A
    WHERE keycol=123)

    You can verify afterwards whether the row was inserted or not by testing
    ROWCOUNT.


    2. Trap the error in your client application.

    3. Check out the IGNORE_DUP_KEY option on the CREATE INDEX statement.


    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  3. #3

    Default Re: error not trapping errors?

    Another method that wouldn't require checking rowcount would be:

    IF EXISTS (SELECT 1 FROM table WHERE keycol=keycol)
    PRINT 'OOPS'
    ELSE
    INSERT ...

    Not much difference, really, from David's #1...


    --
    Aaron Bertrand, SQL Server MVP
    http://www.aspfaq.com/

    Please reply in the newsgroups, but if you absolutely
    must reply via e-mail, please take out the TRASH.


    "Dr Deadpan" <com> wrote in message
    news:google.com... 


    Aaron 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. Trapping datatype errors for web methods
    By Saffola in forum ASP.NET Web Services
    Replies: 2
    Last Post: August 13th, 05:13 PM
  4. Trapping errors from stored procedure calls
    By Robert Carnegie in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 12:37 PM
  5. trapping compile time errors
    By Gerard A.W. Vreeswijk in forum Ruby
    Replies: 5
    Last Post: July 7th, 12:05 PM

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