Errorhandling

ERROR HANDLING OR EXCEPTIONAL HANDLING :

Error handling in SQL Server gives us control over the Transact-SQL code. For example, when things go wrong, we get a chance to do something about it and possibly make it right again.

In SQL Server you can take advantage of TRY...CATCH statements to handle errors. When writing code that handles errors, you should have a TRY block and a CATCH block immediately after it. The TRY block starts with a BEGIN TRY statement and ends with an END TRY statement. Similarly, the CATCH block starts with a BEGIN CATCH statement and ends with an END CATCH statement.

-- 1. We should keep all sql statements in try block, if any error will come then it will throw that error into catch block.

-- 2.Commit will be done in try block.

-- 3.Rollback will be done in catch block.

-- 4.@@error global variable is used to finding an error Suppose @@error = 0 -- then no error @@error <> 0 -- then error is there

-- 5.In error handling we will use below mentioned functions

  error_message()--it gives what is the error is
  error_line()   --it gives line number where error is exactly.
  error_severity()--16--which means we can solve manually.
  error_procedure()--it gives procedure name where the error occures.

ex:

create procedure pr_dept( 
    @deptno int ,
    @dname varchar(100)
    @loc varchar(100)
)
as 
begin
  begin try
    begin tran
      if (@deptno = 10)
      begin
         insert into dept (deptno,dname,location)
         values (@deptno,@dname,@loc)
      end
      else 
      begin
         update DEPT set  dname=@dname where deptno=@deptno
      end

      if @@error = 0
      begin
      commit tran
      select 'Transaction has been completed successfully' as message
      end
    end try

   begin catch
    if @@error <> 0
    begin
    rollback 
    select 'transaction rollbacked' as message,
      @@error_message() as err_message,
      @@error_line() as err_line,
      @@error_severity() as severity,
      @@error_procedure() as err_procedure
    end
end