Creating Log file for Stored Procedure

Introduction

We create stored procedure for our business logic. Some time for our convenience we want to record some message line by line Here I am providing one example to prepare log file for stored procedure.

Example

Creating Log file for Stored Procedure:

In this example I am going to show how to create log file for stored Procedure.

Log File will be created on Database Server.

Give write permission in c: drive or folder where to create the logfile.

Create a table Person

create table Person
(
PID int Primary Key identity (1, 1)
,Person_Name varchar(50)
,Person_Address varchar(150) not null
)

This is test table used in the stored procedure

create procedure CreateLog
(
@Msg varchar(500)
,@Start bit
)
as
begin
declare @cmd varchar(2000)
if(@Start = 1)
begin
set @cmd = 'echo --------------'+ convert(varchar(10),getDate(),101) +'------- --------------- > C:\MyLog.txt'
exec master..xp_cmdshell @cmd
end

set @cmd = 'echo ' + @Msg + ' >> C:\MyLog.txt'
exec master..xp_cmdshell @cmd
end

Now I am going to create a stored procedure in which i will insert one record and then update the Person_Address with null value which will throw error and i will record that in logfile.

create procedure PersonUpdate
(
@PID int
,@Address varchar(50)
)
as
begin
declare @LineNumber varchar(500)

begin transaction

insert into Person values ('Mack','New York')

if(@@error <> 0) goto ErrorHandler

exec CreateLog 'Mack , New York inserted in Person table',1

Update Person set Person_Address = @Address where PID = @PID

if(@@error <> 0) goto ErrorHandler

exec CreateLog 'city has been update For give PID',0

commit transaction
return
ErrorHandler:
begin
Rollback transaction
set @Msg = 'Transaction Rollbacked, Error occured'
exec CreateLog @Msg,0
End
End

now execute the command

exec PersonUpdate 1,null

Now go to C:\MyLog.txt
and open this file messages are recorded here.