Solved

Sql Query help

Posted on 2008-06-09
3
200 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
Sql Permission 6 74
SQL Trigger or Function that updates table with old values 5 54
sql query help 2 62
Caste datetime 2 69
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

696 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