How to set up an audit for the tables using procedures?

As a lot of operations and tasks are performed in a single procedure, it is recommended that the to keep the logs in the table to check in case of any issue or analysis in case if it fails on production execution or for any audit purposes. It is always recommended to keep track of the parameters which were sent to other procedures which are called during the different procedure in the procedure.

Below is the sample for the audit table:

create table AUDIT_Proceduce_Params

(Sno int identity(1,1),

Parent_Procedure_name nvarchar(50),

Child_Procedure_name nvarchar(50),

parameters_input nvarchar(300),

parameters_output nvarchar(300),

EXECUTION_TIME datetime2 default getdate())

For creating a parent procedure:

Create proc [dbo].[usp_Parent_proc]

  as

  begin try

  begin transaction

  select 'Parent Proc Started'

  declare @Emp int,@EmpName nvarchar(30), @Salary numeric(30,2) ,@O_Retvalue int, @O_Retmessage nvarchar(300)

  select @Emp=5 ,@EmpName='EMP5',@Salary=5050505

exec [usp_Child_proc] @Emp,@EmpName,@Salary,@O_Retvalue out,@O_Retmessage out

insert into AUDIT_Proceduce_Params

(Parent_Procedure_name,Child_Procedure_name, parameters_input,parameters_output)

select 'usp_Parent_proc','usp_Child_proc','param1: '+cast(@Emp as nvarchar(50))+' param2: '+@EmpName+' param3: '+cast(@Salary as nvarchar(50)), ' param1: '+cast(@O_Retvalue as nvarchar(50))+' param2: '+@O_Retmessage

  select @Emp=6 ,@EmpName='EMP6',@Salary=4050600

exec [usp_Child_proc] @Emp,@EmpName,@Salary,@O_Retvalue out,@O_Retmessage out

insert into AUDIT_Proceduce_Params

(Parent_Procedure_name,Child_Procedure_name, parameters_input,parameters_output)

select 'usp_Parent_proc','usp_Child_proc','param1: '+cast(@Emp as nvarchar(50))+' param2: '+@EmpName+' param3: '+cast(@Salary as nvarchar(50)), ' param1: '+cast(@O_Retvalue as nvarchar(50))+' param2: '+@O_Retmessage

if( @O_Retvalue= 1)

begin

select 'Child Proc EXECUTED'

 commit transaction

     end

end try

BEGIN CATCH

  select 'Inside Parent Proc CATCH block'

 ROLLBACK transaction

END CATCH

GO

For creating a child procedure:

create proc [dbo].[usp_Child_proc]

(@p_emp int,@P_EmpName nvarchar(30), @P_Salary numeric(30,2) , @O_Retvalue int out, @O_Retmessage nvarchar(300) out)

  as

  begin try

  INSERT INTO Employ VALUES ( @p_emp,@P_EmpName,@P_Salary)

 set @O_Retvalue=1

             set @O_Retmessage='Records Inserted'

             print '40'

end try

BEGIN CATCH

   set @O_Retvalue=0

             set @O_Retmessage='Error Occurred'

END CATCH

On execution of procedure (exec [usp_Parent_proc]) you will get the below outputs:

select * from AUDIT_Proceduce_Params