Exception Handling in SQL Server 2000 and 2005 - How to create a stored procedure in SQL Server 2005 with exception handling: explanation

 

I hope everybody can understand the definition of the CREATE PROCEDURE (as discussed in previous sections).  Further proceeding we have the following.

 

      begin transaction

      insert into emp (empno,ename,sal,deptno)

            values (@empno,@ename,@sal,@deptno)

      commit transaction

 

This is a bit different from the previous sample.  The statements between “begin try” and “end try” will be simply TRIED by SQL Server run time to execute. If the statements between “begin try” and “end try” get executed successfully without any errors, the transaction is saved successfully with the help of COMMIT TRANSACTION.

 

      rollback transaction

      insert into error_log (LogDate,Source,ErrMsg)

            values (getdate(),'sp_emp_insert',error_message())

 

If any error occurs while executing the statements between “begin try” and “end try,” the flow of execution automatically gets jumped to the “catch” block. Within the “catch” block (or between “begin catch” and “end catch”), we simply rollback (in other words, cancel) the transaction and finally log the error to the “error_log” table using a simple INSERT statement (as above).  Another flexibility in SQL Server 2005 is the “error_message().”  It gives us the immediate error message that occurred.

 

From the above code, you can observe that we are trying to eliminate all “goto” statements and make it “structured.”  This really makes our script easily understandable to the depth of any number of exceptions.