Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2010
    Posts
    1

    Unanswered: Quires regarding SQL Stored Procedure

    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.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I don't like to use stored procedure, if it could be written by an SQL statement.

    Your requirement can be generated by a query, like this
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /*******************************
    ***** Begin of sample data *****
    *******************************/
    EMP_TABLE(s_no , emp_id , emp_name , emp_salary) AS (
    VALUES 
      ( 1 , 123 , 'John'   , 100)
    , ( 5 , 123 , 'Sunil'  , 100)
    , ( 7 , 123 , 'Raj'    , 500)
    , (10 , 123 , 'Joseph' , 101)
    )
    /*******************************
    *****   End of sample data *****
    *******************************/
    SELECT changed_column
         , CASE changed_column
           WHEN 'Employee Name' THEN
                 old_name
           WHEN 'Employee Salary' THEN
                 CHAR(old_salary)
           END  AS old_value
         , CASE changed_column
           WHEN 'Employee Name' THEN
                 emp_name
           WHEN 'Employee Salary' THEN
                 CHAR(emp_salary)
           END  AS new_value
      FROM (SELECT e.*
                 , LAG(emp_name  ) OVER(ORDER BY s_no) old_name
                 , LAG(emp_salary) OVER(ORDER BY s_no) old_salary
              FROM emp_table e
           ) e
      JOIN (VALUES 'Employee Name' , 'Employee Salary' ) p(changed_column)
       ON  old_name   <> emp_name   AND changed_column = 'Employee Name'
       OR  old_salary <> emp_salary AND changed_column = 'Employee Salary'
     WHERE emp_id = 123
     ORDER BY
           changed_column
         , s_no
    ;
    ------------------------------------------------------------------------------
    
    CHANGED_COLUMN  OLD_VALUE   NEW_VALUE  
    --------------- ----------- -----------
    Employee Name   John        Sunil      
    Employee Name   Sunil       Raj        
    Employee Name   Raj         Joseph     
    Employee Salary 100         500        
    Employee Salary 500         101        
    
      5 record(s) selected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •