Sql Query help

I have an audit table T with around 11 columns. Say FK, C1,C2,C3.... C10. Here FK is the guid of the corresponding row in the original table. Whenever any data changes in the original table, I write the after image of the data to this audit table.

What I would like to do is write a function/stored proc to return a table. The func or SP takes the "FK" as input parameter. The result set should be like:

DateTime of Change          |           Change Summary
6/2/2008 10:23:34.033      |           C2 (Data --> Changed), C5 (Original --> Changed)
6/5/2008 18:09:00.033      |           C8 (Data --> Changed)
.................
.................
...................

Basically, for each row, I want to summarize the change in the above format, rather than just printing out the audit rows.

dencomAsked:
Who is Participating?
 
vasureddymConnect With a Mentor Commented:
I have done some thing very similar long back which calculates the time difference between each record. but this procedure will be very heavy. if no other expert suggests a better solution, please use this.

You have to use cursors and two variables for each column you wanted to track (@PreviousC1Value, @CurrentC1Value)

1) Declare and open the cursor (with Select * from yourAuditTable where FK = <value>)
2) Declare the required variables
3) Fetch the first row, store the column in @PreviousC1Value (like wise store all the other columns that you are monitoring into the corresponding values)
4) Start the cursor loop
     4a)Fetch the next row, store the column in @CurrentC1Value
     4b)Compare the @PreviousC1Value with @CurrentC1Value and if any difference store this message in to a string variable.  (like wise for all other columns, append to the same string variable with comma separated)
     4c) insert a record into Results table (the time stamp, and the string variable that is build)
     4d) move the @CurrentC1Value into @PreviousC1Value (like wise for all other columns)
5) End the cursor loop
6) close and deallocate cursor
7) select * from ResultsTable

There are many other ways to do this instead of cursors, but cursors is a straight forward method

-
Srinivas
0
 
vasureddymCommented:
what is the difference between Data --> Changed and Original --> Changed
0
 
dencomAuthor Commented:
Yeah, I knew the default option is the cursor. I know you looked at set based soln, but I am going to keep the Q open to see if anybody has any other ideas. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.