?
Solved

Sql Query help

Posted on 2008-06-09
3
Medium Priority
?
227 Views
Last Modified: 2011-09-20
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.

0
Comment
Question by:dencom
  • 2
3 Comments
 
LVL 2

Expert Comment

by:vasureddym
ID: 21749965
what is the difference between Data --> Changed and Original --> Changed
0
 
LVL 2

Accepted Solution

by:
vasureddym earned 2000 total points
ID: 21750039
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
 

Author Comment

by:dencom
ID: 21750879
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question