hi i am looking for code where i want to compare multiple columns which exist in 2 different tables, note that column names are same in both the tables,
i want to pull only modified columns and their values, please find below i have written to achieve the functionality
i am pulling some bit values thru xml data and i want to from those i want to update the entries in the database
what i am doing is i am comparing each and every column with following statement which does not look good to me
-- pulling existing data before save thru below query
select @oldoverid=IsOverride,@oldisvisible=IsShow,@oldishelp=IsShowHelp from (nolock) where id= 21999 and SetId=3433
--converting bit(0 or 1) to true/false for saving in oldvalue/freshvalue variables to be logged into adminactivity table
select
case when @oldisvisible=0 then 'False' else 'True' end ,
case when @oldishelp=0 then 'False' else 'True' end ,
case when @oldoverid=0 then 'False' else 'True' end
-- comparing differnt properties of a element one by one , this is robust , variables starting with new are what i get from xml and want to update
-- in database after comparing with oldvalues of specific columns
if( @newvisible<> @oldvisible) --this is how i am comparing now for different columns
begin
set @oldvalue = 'visible' + '-' + case when @oldvisible=0 then 'False' else 'True' end
set @freshvalue = 'visible' + '-' + case when @newvisible=0 then 'False' else 'True' end
select @oldvalue oldvalue ,@freshvalue newvalue ,@freshvalue 'visiblein- modified'
end
if( @oldoverid <> @newoverride)
begin
set @oldvalue = @oldvalue + ',' + 'AllowOverride' + '-' + case when @oldvisible=0 then 'False' else 'True' end
set @freshvalue = @freshvalue + ',' + 'Allow Override' + ',' + case when @newvisible=0 then 'False' else 'True' end
select @oldvalue 'oldvalue', @freshvalue 'newvalue'
end
end
end