Transaction with StoredProcedure

create procedure dbo.sp_emp_insert

(

      @empno      int,

      @ename      varchar(20),

      @sal  float,

      @deptno int

)

as

begin

declare @Error int

begin transaction

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

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

  set @Error = @@ERROR

  if @Error <> 0 --if error is raised

  begin

      goto LogError

  end

commit transaction

goto ProcEnd

LogError:

rollback transaction

declare @ErrMsg varchar(1000)

select @ErrMsg = [description] from master.dbo.sysmessages

  where error = @Error

insert into error_log (LogDate,Source,ErrMsg)

      values (getdate(),'sp_emp_insert',@ErrMsg)

ProcEnd:

end

GO