How to track error or Log in Stored procedure

• avdhut.k

  SSCrazy

  Points: 2834

  More actions

  December 24, 2013 at 12:41 am

  #287638

  Hi,

  I had a parent SP , in which I am calling other 5 SP one by one.

  For Example,

  Create Temp_Parent_SP

  (

  @para varchar(10)

  )

  As

  Begin

  Exec Sp_Step_one 1

  Exec Sp_Step_Two 2

  Exec Sp_Step_Two 3

  Exec Sp_Step_Two 4

  Exec Sp_Step_Two 5

  end

  This is the Log table I want to update,

  Create Table Temp_Log_File

  (

  Srno Int,

  Activity_Name Varchar(500),

  ExeStatus varchar(20),

  StartDt datetime,

  EndDt Datetime,

  LstModDt datetime,

  Remark Varchar(1000)

  )

  Insert Into Temp_Log_File

  Values (1,'Step One','',NULL,NULL,NULL,''),

  (2,'Step Two','',NULL,NULL,NULL,''),

  (3,'Step Three','',NULL,NULL,NULL,''),

  (4,'Step Four','',NULL,NULL,NULL,''),

  (5,'Step Five','',NULL,NULL,NULL,'')

  Select * from Temp_Log_File

  Against all this fields I want to update the status,

  For example,

  If Sp_Step_one is running then in column,

  ExeStatus = It should be "Processing"

  StartDt = It should be getdate()

  EndDt = Should bo NULL (As it is processing)

  If Sp_Step_one is executed then in column,

  ExeStatus = It should be "Completed"

  EndDt = Should bo getdate

  If Sp_Step_one is Pending then in column,

  ExeStatus = It should be "Pending"

  StartDt = NULL

  EndDt = Null

  If Sp_Step_one has error then in column,

  ExeStatus = It should be "Error"

  StartDt = Start Datetime of SP

  EndDt = Null

  Remark = what is the error in Sp_Step_one

  Please help me,as I need this urgently,

  Thanks in Advacne!!!!

• homebrew01

  SSC Guru

  Points: 55548

  More actions

  December 25, 2013 at 7:22 pm

  #1675912

  You just need to put in some UPDATE statements at each step.