Exception Handling in SQL Server 2000 and 2005 - How to create a stored procedure in SQL Server 2000 with exception handling: source code
(Page 2 of 5 )
Let us try to recollect error handling used in our previous experiences with SQL Server 2000 or before. We can observe that this job is monotonous in SQL Server 2000 because for every statement a local value must be stored, which decreases the clarity of the code and increases the complexity and even the size of the code.
Let us consider the following code.
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
To execute the above program, you need to issue the following statement in query analyzer:
exec sp_emp_insert 1003,'ccc',4000,30
I shall explain the above code in the next section.
/ 46 
