Exception Handling in SQL Server 2000 and 2005 - How to create a stored procedure in SQL Server 2005 with exception handling: source code
(Page 4 of 5 )
Create the same two tables in SQL Server 2005 as specified in the first section. Now, let us modify the previous procedure with a better approach in SQL Server 2005. What follows is the modified code.
DropProcedure dbo.sp_emp_insert
go
createprocedure [dbo].[sp_emp_insert]
(
@empno int,
@ename varchar(20),
@sal float,
@deptno int
)
as
begin
begintry
begin transaction
insert into emp (empno,ename,sal,deptno)
values (@empno,@ename,@sal,@deptno)
commit transaction
endtry
begincatch
rollback transaction
insert into error_log (LogDate,Source,ErrMsg)
values (getdate(),'sp_emp_insert',error_message())
endcatch
end
The execution is very similar to that of the previous code. You simply issue and execute the following statement in SQL Server Management Studio:
exec sp_emp_insert 1003,'ccc',4000,30
The execution again is very similar to what we've previously seen. You simply issue and execute the following statement in SQL Server Management Studio. The explanation for the above code is given in the next section.
/ 46 
