Comparing existing row with the new one

CREATE PROCEDURE InsertRecord
     @col1 int,
     @col2 varchar(50),
     @col3 bit,
     ...
AS

IF NOT EXISTS (SELECT col1, col2, col3, ... FROM myTable WHERE col1 = @col1 AND col2 = @col2 AND col3 = @col3 ...)
BEGIN
     INSERT INTO myTable (col1, col2, col3, ...) VALUES (@col1, @col2, @col3, ...)
     --not sure what you're looking to do with "changes log"...is it a separate table or a column in the data table?
END
GO

changes log is a column in the data table.

ok this part of the procedure is handling the insert if no changes is made.
how about the changes log part?
thanks,
-shane

you can do this
CREATE TABLE ee(
      [userid] [int] identity(1,1),
      [val1] [int] NULL ,
      [val2] [int] NULL ,
      [val3] [int] NULL
) ON [PRIMARY]
CREATE TABLE eeHistory(
      [userid] [int],
      [val1] [int] NULL ,
      [val2] [int] NULL ,
      [val3] [int] NULL
) ON [PRIMARY]


create procedure EE103(@userid int,@val1 int,@val2 int,@val3 int)
as
declare @cnt int
declare @tmp TABLE (
      [userid] [int],
      [val1] [int] NULL ,
      [val2] [int] NULL ,
      [val3] [int] NULL )

insert into @tmp values(@userid,@val1,@val2,@val3)

select * from @tmp
union
select * from ee
where userid = @userid

if  @@rowcount > 1 -- different records update
BEGIN
      insert into eeHistory  --insert rows to historytable
      select * from ee
      where userid = @userid
      update ee
      set val1=@val1,val2=@val2,val3=@val3
      where userid = @userid
END
else
return


I would actually make an update trigger as opposed to the insert into history section
but either way will work.  The reason i do this with the temp table vs a big where clause it in the long run it would be faster since you are doing index seeks.  You do have indexes correct :)

Steve

i dont want to use a second table. i want to keep a log of what has changed in a column in my existing table.
if no changes are made nothing will be done, if one column is updated my log will keep: <columnName:oldValue to newValue> or such notifications. And once again there is no update in my case. If a new record is inserted and there is an old copy of it the old copy will stay teh way it is, the new one will create a new entry with a new version number.
I know the idea but i dont know the implementation. here is the idea :

i will have a trigger on my table that will change insert statement. so i will have instead of insert trigger.
This trigger may call a stored procedure but i dont know know how the parameters will be called or used by the procedure. This procedure will insert the entry if no previous one exists, will not do anything if a previous one exists but there is no changed made, will insert and keep a change log if the previous one exists and some changes made.

i hope i m not putting it all complicated,
thanks
-shane

That just sounds rediculous?  or maybe i just don't understand the purpose.  
are you saying you want a table like this

column1, column2,column3, CHANGESColumn?

then if i insert
1,1,1,null into it it acts like an insert?
then i try and insert 1,1,1 again it does nothing
then i try and insert 1,1,2
i will now have 2 rows in te table
1 that has
1,1,1 null
1,1,2, <column3 changed from 1 to 2>
?

steve

what columns are in the table you are planning on doing this with?
send a create table statement
with some inserts and expected results

Steve

.............
column1, column2,column3, CHANGESColumn?

then if i insert
1,1,1,null into it it acts like an insert?
then i try and insert 1,1,1 again it does nothing
then i try and insert 1,1,2
i will now have 2 rows in te table
1 that has
1,1,1 null
1,1,2, <column3 changed from 1 to 2>
?
.............

this is exactly what i m trying to do. It may not sound right but this is how it is supposed to be. And this should be in all columns of the table that comes from the webform. If you have a code you can go with your little example above. use tbl1:c1,c2 and c3 and i ll change it into my case.
thanks,
-shane

You need to define what makes a row the same so that you can tell when it is different.

if i have records like

1,1,1
1,2,1
3,6,7

and i insert the record 4,2,7 which record am i comparing it to.  You need to define the control fields.  Is there an ID field?

the inserted will always be compared with the last one. that is why i have a version field. the primary key is the combination of 2 columns; myID and VersionNo.
myID may repeat, VersionNo may repeat but their union may not. And i know how to get the last versioned row so dont worry about it.
Think it this way: there is a row to be inserted and there is this last versioned row.
I will compare these 2 and run the logic in my above post.
thanks,
-shane

stevetheski ,
thanks for the code, i tested and its working.
so i have to do:

@updatecol1 = case val1
      when @val1 then null
      else 'val1: '+cast(val1 as varchar(10)) +'->' +cast(@val1 as varchar(10))
      end,

for every column in my table right?
I wish there was an internal function that is comparing 2 rows and generating the differences.
thanks again,
-shane

you are correct.  you will only need to cast the numeric columns thats why i ised ints

i will tell you that this is absolutely crazy :)

i have used histroy tables and hash tables as history tables  but never anything like this

maybe you should consider about this in an upcoming project :)

running such a procedure with 30 columns will not kill the db server right? functionality-wise it will be working but i never thought about performance-wise.
what do you think?

I am trying to get as much burden as i could from the programming side and put it on db server but i m not sure how it handles all these.

thanks,
-shane