Compare field value at the same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Compare field value at the same table

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-03-01 : 22:24:26

Dear All,

I have created AuditTrail table & others SP.

I having issue on the bold red colour section.
Compare field value difference from Temp Table based on ID, dump into [AuditTrail] >> This can be handle by another Stored procedure, but i dono how to perform it.

Please Advise.

Thank you.

Here is my full skeleton:


CREATE TABLE [dbo].[AuditTrail](
	[AuditTrailID] [int] IDENTITY(1,1) NOT NULL,
	[TableName] [varchar](50) NOT NULL,
        [ID] [int] NOT NULL,
        [FieldName] [varchar](50) NOT NULL,
        [OldValue] [varchar](50) NULL,
        [NewValue] [varchar](50) NULL,
	[ActionTaken] [varchar](50) NOT NULL,
	[ActionUser] [varchar](50) NOT NULL,
	[ActionDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

create table test
(
	ID int Identity(1,1),
	productsn nvarchar(100),
	productname nvarchar(100),	
	qty int null,
	unitprice float null
)

insert into test
('SN00001','PA','20','20.00')
insert into test
('SN00002','PB','3','40.00')
insert into test
('SN00004','PC','14','50.00')	
insert into test
('SN00012','PD','13','140.00')
insert into test
('SN00015','PE','18','10.00')			
	

alter PROCEDURE [dbo].[log_CreateTempLogSnapshot](
--exec [log_CreateTempLogSnapshot] 'TestDB','mc','test','tempmc','ID','3,4,5'

    @databasename as nvarchar(100),
    @LoginName as nvarchar(50),
    @SourceTable as nvarchar(100),
    @temp as nvarchar(100),
    @FieldName as nvarchar(100),
    @ID as nvarchar(max)
)
AS

 SET NOCOUNT ON;
  
  DECLARE    @SQLString varchar(max);
  
 SELECT @FieldName=cu.COLUMN_NAME 
 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu 
 WHERE EXISTS ( SELECT tc.* FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
 WHERE tc.CONSTRAINT_CATALOG = @databasename 
 AND tc.TABLE_NAME = @SourceTable AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME )
 
 SET @SQLString = N'select * into '+ @temp +' from '+ @SourceTable +' where  '+ @FieldName +' in (' + @ID  +')';
 EXEC (@SQLString); 
 
 --process of update
 update test set qty=1 where AID=3
 update test set qty=11 where AID=2
 
 --Compare Temp Table With Actual Table after update based on PK
 EXEC dbo.CompareTables @databasename,@SourceTable,@temp,@ID;
  
  --Compare field value difference from Temp Table based on ID, dump into [AuditTrail]
// I having issue here
  
  
  SET NOCOUNT OFF;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-01 : 23:08:52

whats the issue you're having there? can we see code logic used?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-03-01 : 23:18:56

Hi,

Ok. I put in simple explanation:- My final TempTable

eg:

tablename: tempmc
------
TableName ID ProductCode ProductName qty Price
Test 3 SN00013 PC 4 20 -->before edit
tempmc 3 SN00012 PC 2 20 -->after edit

I want to dump the only difference field into [AuditTrail]

Output should dump into [AuditTrail] table:
-------------------
[TableName] [PK] [FieldName] [OldValue] [NewValue] [ActionTaken] [ActionUser] [ActionDate]

Test 3 ProductCode SN00013 SN00012 Update mc 02/03/2012
Test 3 qty 4 2 Update mc 02/03/2012

I need a very general Stored Procedure for comparing. Not the normal sql. So i can apply that to any different structure table.

Please Advise.

Thank you.

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-03-02 : 03:36:48

I try to loop the col_name, But failed.

declare @tab_info table
(
[Col_ID] int primary key
, [Tab_Name] varchar(255)
, [Col_Name] varchar(100)
);

insert into @tab_info (Col_ID,Tab_Name,[Col_Name])
select distinct ac.column_id as [Col_ID]
, so.name as [Tab_Name]
, ac.name as [Col_Name]
from sys.objects so
join sys.all_columns ac
on so.[object_id]=ac.[object_id]
join sys.types st
on ac.system_type_id=st.system_type_id
where so.name=''+@SourceTable+''
and [type]='U'
order by ac.column_id;

DECLARE @c_name varchar(128);

declare cr_cols cursor for
select [Col_Name] from @tab_info;

OPEN cr_cols;
FETCH cr_cols INTO @c_name;
WHILE @@FETCH_STATUS = 0
BEGIN
print [Col_Name];
END
CLOSE c;
DEALLOCATE c;

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-03-02 : 10:15:02

Dear all,

I manage to do until this level:
Getting error
Msg 245, Level 16, State 1, Procedure log_CreateTempLogSnapshot, Line 86
Conversion failed when converting the varchar value 'select PK,FieldName=Max(FieldName),TableName=Max(tblName), OldValue=MAX(OldValue), NewValue=MAX(NewValue),ActionDate=GETDATE(),ActionUser='michealesee',ActionTaken='Update' from ( select PK=AID,FieldName='AID',tblname, OldValue=case when ' to data type int.

Please advise.
Thank you.

Alter PROCEDURE [dbo].[log_CreateTempLogSnapshot](
--exec [log_CreateTempLogSnapshot] 'DB','mc','test','temp','AID','3,4,5','Update'

	@databasename as nvarchar(100),
    @LoginName as nvarchar(50),
    @SourceTable as nvarchar(100),
    @temp as nvarchar(100),
    @FieldName as nvarchar(100),
    @ID as varchar(8000),
    @action as nvarchar(100)
)
AS

 SET NOCOUNT ON;
  
  DECLARE    @SQLString varchar(8000);  
  
 SELECT @FieldName=cu.COLUMN_NAME 
 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu 
 WHERE EXISTS ( SELECT tc.* FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
 WHERE tc.CONSTRAINT_CATALOG = @databasename 
 AND tc.TABLE_NAME = @SourceTable AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME )
 
 SET @SQLString = N'select * into '+ @temp +' from '+ @SourceTable +' where  '+ @FieldName +' in (' + @ID  +')';
 EXEC (@SQLString); 
 
 --process of update the table
 update test set qty=20 where AID=3
 
 --Compare Temp Table With Actual Table after update based on PK
 EXEC dbo.CompareTables @databasename,@SourceTable,@temp,@ID;
  
  --Compare field value difference, dump into [AuditTrail]
  

 declare @tab_info table
 (
 [Col_ID] int primary key
 , [Tab_Name] varchar(255)
 , [Col_Name] varchar(100)
 , [Col_Type] varchar(100)
 );

insert into @tab_info (Col_ID,Tab_Name,[Col_Name],[Col_Type])
 select distinct ac.column_id as [Col_ID]
 , so.name as [Tab_Name]
 , ac.name as [Col_Name]
  , st.name  as [Col_Type]
 from sys.objects so
 join sys.all_columns ac
 on so.[object_id]=ac.[object_id]
 join sys.types st
 on ac.system_type_id=st.system_type_id
 where so.name=''+@SourceTable+''
 and [type]='U' and st.name<>'sysname'
 order by ac.column_id;
 
 DECLARE @count INT , @sum int, @col_name nvarchar(80),@col_type nvarchar(80), @ty int

SET @count = 1 
SELECT @sum=COUNT(*) from @tab_info
WHILE (@count < @sum)
BEGIN


	select  @col_name=[Col_Name],@col_type=[Col_Type] from @tab_info where Col_ID=@count
	
	
	if @col_type='date' 
	begin
		set @ty=1; 
	end
	else
	begin
		set @ty=0;
	end
	

	--insert into AuditTrail 
	--(PK,FieldName,TableName,OldValue,NewValue,ActionDate,ActionUser,ActionTaken) 
	SET @SQLString = N'select PK,FieldName=Max(FieldName),TableName=Max(tblName), OldValue=MAX(OldValue), '
	SET @SQLString = @SQLString +'NewValue=MAX(NewValue),ActionDate=GETDATE(),ActionUser='''+@LoginName+''',ActionTaken=''Update'' ' 
	SET @SQLString = @SQLString +'from '
	SET @SQLString = @SQLString +'( '
	SET @SQLString = @SQLString +'	select PK='+@FieldName+',FieldName='''+@col_name+''',tblname, '
	SET @SQLString = @SQLString +'	OldValue=case when '+@ty+'=1 then CONVERT(nvarchar(10), '+@col_name+' , 103)  else '+@col_name+' end, '
	SET @SQLString = @SQLString +'	NewValue=case when '+@ty+'=1 then null  else 0 end '
	SET @SQLString = @SQLString +'	from tempmichealesee2 where tblName='''+@SourceTable+''' '
	SET @SQLString = @SQLString +'	union all '
	SET @SQLString = @SQLString +'	select PK='+@FieldName+',FieldName='''+@col_name+''',tblname=''-'','
	SET @SQLString = @SQLString +'	OldValue=case when '+@ty+'=1 then null else 0 end , '
	SET @SQLString = @SQLString +'	NewValue=case when '+@ty+'=1 then CONVERT(nvarchar(10), '+@col_name+' , 103) else '+@col_name+' end ' 
	SET @SQLString = @SQLString +'	from tempmichealesee2 where  tblName='''+@temp+''' '
	SET @SQLString = @SQLString +')B '
	SET @SQLString = @SQLString +'group by PK;'
	
	print  @SQLString;
	
	exec(@SQLString);
	
	SET @count = (@count + 1) 
END 

drop table tempmichealesee;
drop table tempmichealesee2;
  
SET NOCOUNT OFF;  
GO

CREATE procedure [dbo].[CompareTables] @databasename nvarchar(100), @table1 nvarchar(100), @table2 nvarchar(100), @ID nvarchar(max)
as
declare @sql nvarchar(max),
        @FieldName nVarchar(max)

SELECT @FieldName=cu.COLUMN_NAME 
 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu 
 WHERE EXISTS ( SELECT tc.* FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
 WHERE tc.CONSTRAINT_CATALOG = @databasename 
 AND tc.TABLE_NAME = @table1 AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME )
 
 

set @sql = 'select * into '+ @table2 +'2 from ( select ''' + @table1 + ''' as tblName, * from
(select * from ' + @table1 + ' where '+ @FieldName +' in(' + @ID +')
except
select * from ' + @table2 + ' where '+ @FieldName +' in(' + @ID +')) x
union all
select ''' + @table2 + ''' as tblName, * from
(select * from ' + @table2 + ' where '+ @FieldName +' in(' + @ID +')
except
select * from ' + @table1 +' where '+ @FieldName +' in(' + @ID +')) x ) A'


exec(@sql)


GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources