Exception Handling in SQL Server 2000 and 2005 - How to create a stored procedure in SQL Server 2000 with exception handling: explanation
(Page 3 of 5 )
In this section, I shall explain to you the source code I presented in the previous section. I shall explain it part by part. Let us start with the following:
create procedure dbo.sp_emp_insert
The above line simply specifies the name of the stored procedure to create, namely “sp_emp_insert.”
@empno int,
@ename varchar(20),
@sal float,
@deptno int
The above are the parameters which hold all values passed (when execution) to the stored procedure “sp_emp_insert.”
declare @Error int
The above statement simply declares a variable @Error of type integer to hold the error which may occur, while executing the stored procedure.
begin transaction
insert into emp (empno,ename,sal,deptno)
values (@empno,@ename,@sal,@deptno)
Here, we need to understand and must be familiar with word transaction, which means “process must be executed either in its entirety or not at all.” Transactions should either end with a COMMIT or ROLLBACK statement. If it ends with a COMMIT statement, all the changes made to the database are permanent. If the transaction fails, or ends with a ROLLBACK, none of the statements takes effect. The above INSERT statement tries to insert a row of values into the table “emp” as part of transaction.
set @Error = @@ERROR
“@@ERROR” is simply a built-in global variable mainly used to know the status of errors within our code. It contains the error id produced by the last SQL statement. If no error occurs with the latest DML statement, the value of @@ERROR would be zero. The value of @@Error must be assigned to a user-defined variable (like @Error) before proceeding to any other work. It is a rule of thumb in SQL Server 2000.
if @Error <> 0 --if error is raised
begin
goto LogError
end
According to the above IF condition, if there exists a non-zero value in @Error, some error has occurred and I wanted to log it immediately. Once the “goto” gets executed, the flow of execution immediately jumps (thereby skipping all the statements in between) to the statement titled “LogError.”
commit transaction
goto ProcEnd
According to above statements, if no error exists, we proceed with committing (or making changes permanent) and immediately jump to the end of the procedure.
LogError:
rollback transaction
declare @ErrMsg varchar(1000)
select @ErrMsg = [description] from master.dbo.sysmessages
where error = @Error
The above set of statements gets executed if and only if any error gets raised. The set of statements include the rolling back issue (which cancels the transaction). And we finally log it using the following INSERT statement based on the error information retrieved by the above SELECT statement.
insert into error_log (LogDate,Source,ErrMsg)
values (getdate(),'sp_emp_insert',@ErrMsg)
As we have seen the basics of error handling in SQL Server 2000, we shall now try to achieve the same in SQL Server 2005 (but with greater flexibility and structured exception handling).
/ 46 
