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.
