Tell SQL Server the error is "handled" in try...catch

Question

I'd like to indicate to SQL Server 2005, in my BEGIN CATCH...END CATCH block that the error is "handled"... That is, clear the error.

Is that possible? Consider this:

begin transaction 
  begin try 
    begin transaction 

      select cast('X' as bit) 
    commit transaction 
   end try 
 begin catch rollback transaction 

   select error_number(), error_message() 
 end catch 

 commit transaction 

This results in the following:

(0 row(s) affected)

(No column name)    (No column name)
245 Conversion failed when converting the varchar value 'X' to data type bit.

(1 row(s) affected)
Msg 3902, Level 16, State 1, Line 13
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Thanks. A.

Answer 1

Yes -

BEGIN TRY
    EXECUTE your_stored_procedure
END TRY

BEGIN CATCH

END CATCH;

See this article for more details.