Professional Web Applications Themes

Error handling in SQL - Microsoft SQL / MS SQL Server

Hi. I came across following stored procedure. CREATE PROCEDURE [dbo].[pr_tblUser_Delete] iId int, iErrorCode int OUTPUT AS SET NOCOUNT ON DELETE FROM [dbo].[tblUser] WHERE [Id] = iId -- Get the Error Code for the statement just executed. SELECT iErrorCode=ERROR My understanding is that if something go wrong with DELETE statement (record locked for example ). Then SQL throws exception which is propagated to ADO or (ADO.NET , or whatever library is used). And as I understand the second statement is never going to be executed. Am I correct? Thanks. George...

  1. #1

    Default Error handling in SQL

    Hi.
    I came across following stored procedure.
    CREATE PROCEDURE [dbo].[pr_tblUser_Delete]

    iId int,

    iErrorCode int OUTPUT

    AS

    SET NOCOUNT ON

    DELETE FROM [dbo].[tblUser] WHERE [Id] = iId

    -- Get the Error Code for the statement just executed.

    SELECT iErrorCode=ERROR



    My understanding is that if something go wrong with DELETE statement (record
    locked for example ). Then SQL throws exception which is propagated to ADO
    or (ADO.NET , or whatever library is used).
    And as I understand the second statement is never going to be executed.

    Am I correct?

    Thanks.
    George


    George Ter-Saakov Guest

  2. #2

    Default Re: Error handling in SQL

    "George Ter-Saakov" <wehotmail.com> wrote in message
    news:u3w5DyKRDHA.2096TK2MSFTNGP12.phx.gbl...
    > Hi.
    > I came across following stored procedure.
    > CREATE PROCEDURE [dbo].[pr_tblUser_Delete]
    >
    > iId int,
    >
    > iErrorCode int OUTPUT
    >
    > AS
    >
    > SET NOCOUNT ON
    >
    > DELETE FROM [dbo].[tblUser] WHERE [Id] = iId
    >
    > -- Get the Error Code for the statement just executed.
    >
    > SELECT iErrorCode=ERROR
    >
    >
    >
    > My understanding is that if something go wrong with DELETE statement
    (record
    > locked for example ).
    This is not an error unless you have previously issued a
    SET LOCK_TIMEOUT X
    with X > -1. The default behaviour is for the DELETE statement to wait to
    acquire the required lock.
    >Then SQL throws exception which is propagated to ADO
    > or (ADO.NET , or whatever library is used).
    > And as I understand the second statement is never going to be executed.
    Unless this is an extremely severe error, or a data conversion error, the
    next statement will be executed.

    That's why you have the line
    SELECT iErrorCode=ERROR

    It captures the error value, and then you can "GOTO" an error handler or
    just "RETURN iErrorCode".

    David


    David Browne Guest

Similar Threads

  1. Error handling
    By Allen Browne in forum PHP Programming
    Replies: 31
    Last Post: April 17th, 07:35 AM
  2. CF Error Handling
    By JakeFlynn in forum Macromedia ColdFusion
    Replies: 0
    Last Post: March 16th, 01:29 AM
  3. better error handling
    By Matty in forum PHP Development
    Replies: 1
    Last Post: September 9th, 12:36 AM
  4. better error handling...?
    By Kaosweaver in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 17th, 02:15 AM
  5. Error handling and custom error messages
    By Cedomir Markovic in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 10th, 09:00 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