Error Handling in SQL Server 2005 stored Procedures
Errors may occur in T-SQL in several possible ways, including hardware failures, network failures, bugs in programs, out of memory and other reasons.
We may not know which error would at anytime. But we need to handle all such errors and provide some meaningful messages to the user instead of making the user confused with error messages that are impossible to understand.
An exception is generally a runtime error which gets raised by SQL Server runtime when a T-SQL block is in the process of execution. Handling the exception is something like trapping the error (or exception) and inserting that error into the error_log table including date, error message, and other details. Storing error messages in the error_log table makes it easy to trace for future maintenance.
It doesn’t mean that errors are in the table only for maintenance; we can take certain actions programmatically when an error occurs. Error handling is a very monotonous task and we should make it as simple as possible. If error handling is too complex, bugs might creep into the error handling and should be tested after each statement.
Another special case is the use of transactions. We need to issue a “ROLLBACK TRANSACTION” to undo a transaction when an error creeps in.
Example :
Lets have a Simple Tables mytable and myerrorlog
————————————————
CREATE TABLE [dbo].[mytable] (
[myid] [int] NOT NULL ,
[myname] [varchar] (50),
[age] [int] NULL ,
[gender] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[myerrorlog]
(
[LogDate] [datetime] NULL ,
[Source] [varchar] (50),
[ErrMsg] [nvarchar] (255) ,
[Remarks] [varchar] (50)
)
GO
Stored Procedure in SQL SERVER 2005 with Exception Handling
———————————————————
createprocedure [dbo].[sp_mytable_insert]
(
@myid int,
@myname varchar(20),
@age int,
@gender varchar(20)
)
as
begin
begintry
begin transaction
insert into mytable(myid,myname,age,gender)
values (@myid,@myname,@age,@gender)
commit transaction
endtry
begincatch
rollback transaction
insert into myerrorlog (LogDate,Source,ErrMsg)
values (getdate(),’sp_emp_insert’,error_message())
endcatch
end
Explanation
———–
The statements between “begin try” and “end try” will be simply TRIED by SQL Server run time to execute. If the statements between “begin try” and “end try” get executed successfully without any errors, the transaction is saved successfully with the help of COMMIT TRANSACTION.
If any error occurs while executing the statements between “begin try” and “end try,” the flow of execution automatically gets jumped to the “catch” block. Within the “catch” block (or between “begin catch” and “end catch”), we simply rollback (in other words, cancel) the transaction and finally log the error to the “error_log” table using a simple INSERT statement (as above). Another flexibility in SQL Server 2005 is the “error_message().” It gives us the immediate error message that occurred.
From the above code, you can observe that we are trying to eliminate all “goto” statements and make it “structured.” This really makes our script easily understandable to the depth of any number of exceptions.
