Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94

    Unhappy Unanswered: Try.. Catch Block

    HI,
    i'm trying to execute some sql using the Try.. Catch blocks.

    Following code does not execute in Catch Block
    Code:
    Begin
    	begin try
    		insert into dbo.Test values  (1,'aaa') 
                              -- here we are inserting int value in identity field...
    	END TRY
    	Begin catch
    		PRINT 'TEST'
    		SELECT 
    			ERROR_NUMBER() AS ErrorNumber,
    			ERROR_SEVERITY() AS ErrorSeverity,
    			ERROR_STATE() as ErrorState,
    			ERROR_PROCEDURE() as ErrorProcedure,
    			ERROR_LINE() as ErrorLine,
    			ERROR_MESSAGE() as ErrorMessage; 
    	 END Catch
    End
    GO
    Whereas the following block works fine and the Catch block executes.
    Code:
    Begin
    	begin try
    		Select 1/0 
                              --This causes an error.
    	END TRY
    	Begin catch
    		PRINT 'TEST'
    		SELECT 
    			ERROR_NUMBER() AS ErrorNumber,
    			ERROR_SEVERITY() AS ErrorSeverity,
    			ERROR_STATE() as ErrorState,
    			ERROR_PROCEDURE() as ErrorProcedure,
    			ERROR_LINE() as ErrorLine,
    			ERROR_MESSAGE() as ErrorMessage; 
    	 END Catch
    End
    GO

    Any idea why is it so?
    In GOD we believe. Everything else we Test!

  2. #2
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    I'm using SQL 2005 for above code sample which supports Try... Catch blocks in SQL.
    In GOD we believe. Everything else we Test!

  3. #3
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    Hi
    Try – catch is construction like TRY - CATCH in Java or TRY – EXCEPT in Delphi (not like TRY - FINALLY).
    Catch block is executed only if there is an error in try section.
    There are few good examples in SQL Server help.

    You can test it using following code.

    Code:
    begin
      begin try
        insert into dbo.Test values  (1,'aaa') 
                              -- here we are inserting int value in identity field...
        
        raiserror('It works', 16, 10)
      end try
      begin catch
        print 'TEST'
        select 
          ERROR_NUMBER() AS ErrorNumber,
          ERROR_SEVERITY() AS ErrorSeverity,
          ERROR_STATE() as ErrorState,
          ERROR_PROCEDURE() as ErrorProcedure,
          ERROR_LINE() as ErrorLine,
          ERROR_MESSAGE() as ErrorMessage; 
       end Catch
    End
    Last edited by Kris Zywczyk; 12-11-06 at 09:38.
    Regards
    Kris Zywczyk

  4. #4
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    I'll rephrase my query for more precise definition.
    when i'm inserting int value into identity column it throws an error but does not execute the catch block.

    In the second snippet also, we get an error and Catch block gets executed.

    This is what irks...
    In GOD we believe. Everything else we Test!

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Did you try checking the severity level of the error received in your initial code block? Catch only fires for severity levels greater then 10...

    Another thought is try..catch does not catch compile errors. Whereas this is an issue that violates the basic constraints of the table, I would expect the statement to never execute and would likely be considered a "compilation" error. Don't take my word for it though, that's just a hunch.

    Edit:
    Ok, I'm leaning even more heavily on a compile error at this time.

    Example:

    Code:
    CREATE PROCEDURE usp_TryCatchTest
    AS
    
    
    IF (object_id(N'#temp') IS NULL)
    BEGIN
    create table #temp (col1 int identity, col2 int)
    END
    
    	INSERT INTO #temp 
    	VALUES(1,1)  -- now this is strange, because QA doesn't bomb with explicit values
    	
    
    drop table #temp
    
    GO
    
    BEGIN TRY
    	EXEC usp_TryCatchTest
    END TRY
    BEGIN CATCH
    	select 
          ERROR_NUMBER() AS ErrorNumber,
          ERROR_SEVERITY() AS ErrorSeverity,
          ERROR_STATE() as ErrorState,
          ERROR_PROCEDURE() as ErrorProcedure,
          ERROR_LINE() as ErrorLine,
          ERROR_MESSAGE() as ErrorMessage; 
    END CATCH

    The part I still can't answer is why you can define "VALUES (1,1)" when creating usp_TryCatchTest but you can't within the TRY..CATCH block itself...

    EDIT2:

    Even stranger... this works:

    Code:
    	begin try
    		EXEC ('insert into dbo.Test values  (1,''aaa'') ')
                              -- here we are inserting int value in identity field...
    	END TRY
    	Begin catch
    		PRINT 'TEST'
    		SELECT 
    			ERROR_NUMBER() AS ErrorNumber,
    			ERROR_SEVERITY() AS ErrorSeverity,
    			ERROR_STATE() as ErrorState,
    			ERROR_PROCEDURE() as ErrorProcedure,
    			ERROR_LINE() as ErrorLine,
    			ERROR_MESSAGE() as ErrorMessage; 
    	 END Catch
    
    GO
    The above would lead me to believe it is a bug in how the error message is "returned" from your insert statement. When wrapped up in EXEC(), EXEC() actually returns the error to your TRY clause, whereas when executed directly, it seems the error bombs out of the TRY..CATCH statement entirely.

    I think this again lends credence to the compilation consideration for TRY..CATCH:
    The CATCH block does not handle compile errors, such as syntax errors, that prevent a batch from executing. Also, object name resolution errors are not handled by the CATCH block.

    The IDENTITY violation would prevent the batch from compiling. However, if fired from a call to EXEC(), either via stored proc or dynamic sql, then the execution failure actually occurs within the EXEC() call as opposed to your original TRY..CATCH block. EXEC() then halts execution and returns a code back to your TRY..CATCH block, which detects the error and handles it accordingly. If the statement was executed directly, your calling TRY..CATCH block wouldn't handle it because it's a compile error occuring at the base TRY..CATCH level.
    Last edited by Teddy; 12-11-06 at 16:00.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280

    Look in the BOL!

    This quote from the BOL answers all questions above:

    The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:
    • Compile errors, such as syntax errors, that prevent a batch from executing.
    • Errors that occur during statement-level recompilation, such as object name resolution errors that happen after compilation due to deferred name resolution.


    These errors are returned to the level that ran the batch, stored procedure, or trigger.
    Lex

Posting Permissions

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