Quires regarding SQL Stored Procedure

rammanu · New User Joined: 11 Oct 2007 Posts: 5 Location: Chennai

I have one table called Employee and detail would like below

EMP_TABLE
S.No Emp_Id EmP_Name Emp_Salary
1 123 John 100
5 123 Sunil 100
7 123 Raj 500
10 123 Joseph 101

When ever i do the update on the columns like Emp_Name or Emp_Salary a new
record is inserted into the same table with new S.No
For Ex. Emp_Name "John" Changed to "Sunil" new record is inserted into the table with S.No"5".
Note:Emp_ID will remain same and it can't be changed/updated.

Now My requirement is "I should generate the report(using Cursors in SQL Stored procedure)
for audit log" for the "employee_Id 123" The report should be like below

Changed Field Old Value New value
Employee Name John Sunil
Employee Name Sunil Raj
Employee Nmae Raj Joseph
Employee Salary 100 500
Employee Salary 500 101

Please help how I can achieve this.

manikawnth · New User Joined: 07 Feb 2007 Posts: 61 Location: Mumbai

I would suggest this:
DECLARE C CURSOR FOR
SELECT S.NO, EMP_ID, EMP_NAME, EMP_SALARY
FROM EMP_TABLE
GROUP BY EMP_ID
ORDER BY S.NO;

Fetch each row, store it in working storage, fetch next row.
Compare the EMP_IDs of current and previous. Both should be same. Identify the changed value prepare a report.
As simple as that.

I assume that for each update the insert is done according the increasing order of S.No.

Manikanth
P.S: Do I make any sense or Am I completely wrong?