Error Logging in SQL Server procedures
SQL server offers a programming base, called Stored Procedures, where in you can execute a sequence. But some times, there will be errors when executing statements in bulk. So we need to track those errors too.
Error logging involves 3 steps:
– Creating a table to log errors (one time activity)
– Creating a seperate procedure to log necessary information
– Using that procedure, at appropriate stages, by passing necessary parameters
———————————
Creating a table to log errors
——————————–
Let us first figure out the data we needed to track errors. We need to know the name of the Procedure; in that, the sql statement which gave the error; the error code; the message related to the error code; and time at which the error occured. So our database table can be
CREATE TABLE [Error_Log](
[Record] [bigint] IDENTITY(1,1) NOT NULL, –for serial numbers
[Error_Object] [varchar](50), –the name of the stored procedure
[Execution_Pointer] [varchar](100) NULL, –the stage at which the error occured
[Error_Code] [int] NULL, — the error code generated
[Error_Message] [varchar](1000), –the message of the error
[Error_DateTime] [datetime] NULL, –the moment the error occured
CONSTRAINT [PK_Error_Log] PRIMARY KEY CLUSTERED ( [Record] ASC ) ON [PRIMARY]
) ON [PRIMARY]
Execution_Pointer is a marker used after sql statements. Tracking this will help the programmers to trace the sql statements. We will see the implementation shortly.
—————————————————-
Creating a seperate procudure to log necessary information
—————————————————-
Now we need to write a generic procedure to log errors.
create procedure ksp_log_error
@p_procedure_name varchar(50),
@p_pointer varchar(100),
@p_error_code bigint
as
begin
declare
@v_error_message varchar(1000)
–know the error message from sysmessages table
select @v_error_message=description from master..sysmessages
where error= @p_error_code
–if no message exists, use error code
if @v_error_message is null
set @v_error_message=@p_error_code
–insert into table
insert into Error_Log(Error_Object, Execution_pointer, Error_Code, Error_message, Error_Datetime)
values(@p_procedure_name, @p_pointer, @p_error_code, @v_error_message, getdate())
end
————————–
Using the above procedure
————————–
Finally the procedure should be called. For this, I will give you an example. Suppose, we need to register a user into the database. For this we will use transactions and @@ERROR variable. Transactions are useful, especially when dealing with data. @@ERROR has the last occured error number. Using these two I will construct a procedure. I have written the comments where ever necessary.
create procedure register_user
@p_user_id varchar(50),
@p_password varchar(50),
@p_name varchar(100),
@p_email_id varchar(150)
as
begin
declare
@v_count int,
@v_proc_name varchar(50),
@v_pointer varchar(50),
–set primary variables
set @v_proc_name=’register_user’ –name of this procedure
–check if the user is already registered. This is useful to avoid primary key conflicts while inserting
select @v_count=count(*) from Users where User_ID=@p_user_id
–if the user is already registered, stop proceeding
if(@v_count<>0)
begin
select ‘User already registered’ as [Error]
return –stop the execution
end
–if the user is not registered, register him/her.
begin tran
–issue the insert statement
insert into Users values(@p_user_id, @p_password, @p_name, @p_email_id)
set @v_pointer=’insert statement -1′
–check if there is some error
if(@@ERROR<>0)
begin
rollback tran
–call the error logging procedure and pass parameters
exec ksp_log_error @v_proc_name, @v_pointer, @@ERROR
–stop
return
end
–if ok, commit tran
commit tran
–send back the message
select ‘User added successfully’ as [Message]
end
As a programmers perspective, I think this explanation would be enough for another programmer to understand. If you are a beginner, I strongly recommend searching information further till you understand.
If you want to know about Error Logging futher, go to http://www.sommarskog.se/error-handling-I.html