Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Wink Unanswered: Help needed with error handling please [resolved thankyou]

    Hi all,

    I am familiar with Oracle, but have had to do some SQL development. I have written a test stored procedure and have noticed that the proc does not raise an exception on incountering errors (like pk violation/fk violation, not null violations etc.).
    The following is my procedure.
    Code:
    ALTER PROCEDURE TEST_DELPHI
    (
    	@USER_ID varchar(10),
    	@CREATED_REPLICA varchar(32),
    	@CREATED_TICK    int,
    	@CREATED_DATE    datetime,
    	@UPDATED_REPLICA varchar(32),
    	@UPDATED_TICK    int,
    	@UPDATED_DATE    datetime
    )
    AS
    BEGIN
    
    	update REG_USER_PROFILE_USERS set
    		USER_ID = @USER_ID; 
    
    		insert into REG_USER_PROFILE_USERS(USER_ID)
    		values(@USER_ID);
    
    		select @created_tick = 1/0;
    
    END;
    The REG_USER_PROFILE_USERS table has other columns which are defined not null, so the insert should cause an exception and stop executing, passing control back to the caller (well at least thats what every other language does). Instead, the procedure continues executing the next statement (1/0) which should also cause failure. But alas, I never receive a exception. Instead, I just get the following message outputs:
    Code:
    Msg 515, Level 16, State 2, Procedure TEST_DELPHI, Line 29
    Cannot insert the value NULL into column 'USER_PROFILE_ID', table 'MicroCombud.dbo.REG_USER_PROFILE_USERS'; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 8134, Level 16, State 1, Procedure TEST_DELPHI, Line 32
    Divide by zero error encountered.
    While I actually get a message to tell me something went wrong, that just isn't good enough! I cannot have one statement fail and have the procedure continue onto the next statement.

    I.e. how do I get a scenario in which if the insert fails for any reason whatsoever, then the no further statements will run (i.e. 1/0 does not happen), the procedure stops executing and I get an Exception thrown in the calling code?

    Please anything would be helpful right now...
    Last edited by dayneo; 05-27-10 at 17:15. Reason: Status update

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Look up Try and Catch. Not all errors can be caught, and this will only work for SQL 2005 and higher.

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Thanks,
    Try...Catch in combination with RAISERROR has solved my problem. It seems that M$ recons that many errors don't need stop your procedure by default (all with severity less than 10) or if your error occurs after the first statement succeeds.

    Using Try...Catch with a RAISERROR call in the CATCH block will ensure a predictable result. Very important is that you force the severity level up to a value between 11 and 19 in the RAISERROR call.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Severities 1-10 are usually information messages. Most errors I see in procedures are severity 16 or so. And yes, I have had to re-educate my boss about severity 1 means no issue.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Severity 16 is the best to use for user thrown errors (16 indicates a SQL error that can be fixed by the user IIRC). If your are rethrowing an error then use the return from ERROR_SEVERITY().
    Also note that you can change the behaviour of called objects using TRY...CATCH. If you had wrapped your call to the procedure in a TRY...CATCH (and left your procedure unmodified) it would have exited after the first error.

  6. #6
    Join Date
    Jun 2010
    Posts
    1
    Quote Originally Posted by dayneo View Post
    Hi all,

    I am familiar with Oracle, but have had to do some SQL development. I have written a test stored procedure and have noticed that the proc does not raise an exception on incountering errors (like pk violation/fk violation, not null violations etc.).
    The following is my procedure.
    Code:
    ALTER PROCEDURE TEST_DELPHI
    (
    	@USER_ID varchar(10),
    	@CREATED_REPLICA varchar(32),
    	@CREATED_TICK    int,
    	@CREATED_DATE    datetime,
    	@UPDATED_REPLICA varchar(32),
    	@UPDATED_TICK    int,
    	@UPDATED_DATE    datetime
    )
    AS
    BEGIN
    
    	update REG_USER_PROFILE_USERS set
    		USER_ID = @USER_ID; 
    
    		insert into REG_USER_PROFILE_USERS(USER_ID)
    		values(@USER_ID);
    
    		select @created_tick = 1/0;
    
    END;
    The REG_USER_PROFILE_USERS table has other columns which are defined not null, so the insert should cause an exception and stop executing, passing control back to the caller (well at least thats what every other language does). Instead, the procedure continues executing the next statement (1/0) which should also cause failure. But alas, I never receive a exception. Instead, I just get the following message outputs:
    Code:
    Msg 515, Level 16, State 2, Procedure TEST_DELPHI, Line 29
    Cannot insert the value NULL into column 'USER_PROFILE_ID', table 'MicroCombud.dbo.REG_USER_PROFILE_USERS'; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    Msg 8134, Level 16, State 1, Procedure TEST_DELPHI, Line 32
    Divide by zero error encountered.
    While I actually get a message to tell me something went wrong, that just isn't good enough! I cannot have one statement fail and have the procedure continue onto the next statement.

    I.e. how do I get a scenario in which if the insert fails for any reason whatsoever, then the no further statements will run (i.e. 1/0 does not happen), the procedure stops executing and I get an Exception thrown in the calling code?

    Please anything would be helpful right now...
    _______________________________________

    If i am not wrong the reason behind this error rise"
    Msg 515, Level 16, State 2, Procedure TEST_DELPHI, Line 29
    Cannot insert the value NULL into column 'USER_PROFILE_ID', table 'MicroCombud.dbo.REG_USER_PROFILE_USERS'; column does not allow nulls. INSERT fails.
    The statement has been terminated
    ." is
    As your column 'USER_PROFILE_ID' does not allow nulls, it means that its set as 'NOT NULL' AND error came due to it does not have any default value or auto increment value is not set.,
    and the second error
    Msg 8134, Level 16, State 1, Procedure TEST_DELPHI, Line 32
    Divide by zero error encountered
    .
    As your this Select statement "select @created_tick = 1/0;" shows that your are trying to divide 1 by 0 that is not possible.

    Hope this will help you.

    Enjoy
    Attached Thumbnails Attached Thumbnails Identity.gif  

Posting Permissions

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