Solved

Sql Query help

Posted on 2008-06-09
3
192 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 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL HELP 2 88
Insert statement is inserting duplicate records 15 61
Change variables in SQL table 6 87
How to use TOP 1 in a T-SQL sub-query? 14 44
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

777 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