动态获取表每一列变更记录
今天做了一个存储过程
可实现对人事基本资料表中的所有栏位进行check，若有变化，则显示出来
参数：emp_id
原理：在服务器上有两个数据库备份，对比 emp_info 这两张表中相同emp_id的记录 ，
若某一列值不同，则显示出来。
主要问题点：
1.若不能用 OPENDATASOURCE，则可以通过以下sql开启权限。
[sql] view plaincopy
- exec sp_configure 'show advanced options',1
- reconfigure
- exec sp_configure 'Ad Hoc Distributed Queries',1
- reconfigure
使用完成后，关闭Ad Hoc Distributed Queries：
[sql] view plaincopy
- exec sp_configure 'Ad Hoc Distributed Queries',0
- reconfigure
- exec sp_configure 'show advanced options',0
- reconfigure
2.对比时，注意栏位编码不同，需要使用 COLLATE Chinese_PRC_CI_AS
3.对比时，需要注意对栏位null值的处理。
4.动态sql的使用。
USE [EasyHRO_ContinentalChina_20120222]
GO
/****** Object: StoredProcedure [dbo].[sp_get_emp_info_diff] Script Date: 04/18/2012 13:27:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[sp_get_emp_info_diff]
@emp_id nvarchar(50)
--WITH ENCRYPTION
AS
BEGIN
/*
select *
from
OPENDATASOURCE('SQLOLEDB','Data Source=CDPR9B0B6A;User ID=sa;Password=Init1234').EasyHRO_ContinentalChina_20120221.[dbo].[emp_info] a
where emp_id=@emp_id
select distinct xtype from dbo.syscolumns where id=object_id('emp_info') and name not in('emp_id','emp_workid')
select * from systypes
where systypes.xusertype in(56,59,61,104,231)
*/
declare @col_name nvarchar(20);
declare @col_type_id int;
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tb') and type='U')
begin
drop table #tempcitys
end
create table #tb(emp_id int ,
emp_workid nvarchar(200) ,
emp_name nvarchar(200),
old_valus nvarchar(200),
new_valus nvarchar(200),
[col_name] nvarchar(200),
col_cname nvarchar(200)
)
declare cur cursor fast_forward for
select name,xusertype from dbo.syscolumns where id=object_id('emp_info') and name not in('emp_id','emp_workid');
open cur;
fetch next from cur into @col_name,@col_type_id;
while @@fetch_status=0
begin
--做你要做的事
DECLARE @dongtaisql nVARCHAR(4000);
if(@col_type_id=231)--nvarchar
begin
set @dongtaisql = 'insert into #tb select a.emp_id,a.emp_workid,a.emp_name,a.'+@col_name+',b.'+@col_name+','''+@col_name+''','''' from emp_info a,'
+' OPENDATASOURCE(''SQLOLEDB'',''Data Source=CDPR9B0B6A;User ID=sa;Password=Init1234'').EasyHRO_ContinentalChina_20120221.[dbo].[emp_info] b '
+' where a.emp_id=b.emp_id and isnull(a.'+@col_name+','''')<>isnull(b.'+@col_name +','''') COLLATE Chinese_PRC_CI_AS '
+' and a.emp_id=@emp_id '
--+' and a.emp_id='+QUOTENAME(@emp_id, '''')
end
else if(@col_type_id=56 or @col_type_id=59 or @col_type_id=104)--int,real,bit
begin
set @dongtaisql = ' insert into #tb select a.emp_id,a.emp_workid,a.emp_name,a.'+@col_name+',b.'+@col_name+','''+@col_name+''','''' from emp_info a,'
+' OPENDATASOURCE(''SQLOLEDB'',''Data Source=CDPR9B0B6A;User ID=sa;Password=Init1234'').EasyHRO_ContinentalChina_20120221.[dbo].[emp_info] b '
+' where a.emp_id=b.emp_id and isnull(a.'+@col_name+',0)<>isnull(b.'+@col_name+',0) '
+' and a.emp_id=@emp_id '
--+' and a.emp_id='+QUOTENAME(@emp_id, '''')
end
else if(@col_type_id=61)--datetime
begin
set @dongtaisql = ' insert into #tb select a.emp_id,a.emp_workid,a.emp_name,a.'+@col_name+',b.'+@col_name+','''+@col_name+''','''' from emp_info a,'
+' OPENDATASOURCE(''SQLOLEDB'',''Data Source=CDPR9B0B6A;User ID=sa;Password=Init1234'').EasyHRO_ContinentalChina_20120221.[dbo].[emp_info] b '
+' where a.emp_id=b.emp_id and isnull(a.'+@col_name+',''1900-01-01'')<>isnull(b.'+@col_name+',''1900-01-01'') '
+' and a.emp_id=@emp_id '
--+' and a.emp_id='+QUOTENAME(@emp_id, '''')
end
else
begin
set @dongtaisql = ' insert into #tb select a.emp_id,a.emp_workid,a.emp_name,CONVERT(nvarchar(10),a.'+@col_name+',112),CONVERT(nvarchar(10),b.'+@col_name+',112),'''+@col_name+''','''' from emp_info a,'
+' OPENDATASOURCE(''SQLOLEDB'',''Data Source=CDPR9B0B6A;User ID=sa;Password=XXXX'').EasyHRO_ContinentalChina_20120221.[dbo].[emp_info] b '
+' where a.emp_id=b.emp_id and a.'+@col_name+'<>b.'+@col_name
+' and a.emp_id=@emp_id '
--+' and a.emp_id='+QUOTENAME(@emp_id, '''')
end
print @dongtaisql
--exec @dongtaisql
--insert into #tb
EXEC SP_EXECUTESQL @dongtaisql, N'@emp_id nvarchar(50)',@emp_id
-- exec(
--'select a.emp_id,a.emp_workid,a.emp_name,a.'+@col_name+',b.'+@col_name+' from emp_info a,'
--+' OPENDATASOURCE(''SQLOLEDB'',''Data Source=CDPR9B0B6A;User ID=sa;Password=XXXX'').EasyHRO_ContinentalChina_20120221.[dbo].[emp_info] b '
--+' where a.emp_id=b.emp_id and a.'+@col_name+'<>b.'+@col_name
--+' and a.emp_id='+cast(@emp_id as nvarchar)+' ')
fetch next from cur into @col_name,@col_type_id;
end
close cur;
deallocate cur;
delete from #tb
where emp_id is null
update #tb
set col_cname=
(case when exists(select pla_lan_001 from pla_language
where pla_lan_code =[col_name] COLLATE Chinese_PRC_CI_AS and comp_lvcode=10001000)
then (select pla_lan_001 from pla_language
where pla_lan_code =[col_name] COLLATE Chinese_PRC_CI_AS and comp_lvcode=10001000)
else
(select pla_lan_001 from pla_language
where pla_lan_code =[col_name] COLLATE Chinese_PRC_CI_AS and comp_lvcode=1)
end
)
select emp_id,emp_workid,
emp_name,
case when isdate(old_valus)=1 then CONVERT(nvarchar(10),old_valus,121) else old_valus end as old_valus,
case when isdate(new_valus)=1 then CONVERT(nvarchar(10),new_valus,121) else new_valus end as new_valus,
[col_name],
col_cname
from #tb
/*
select a.emp_id,a.emp_workid,a.emp_name,a.emp_actid,b.emp_actid from emp_info a,
OPENDATASOURCE('SQLOLEDB','Data Source=CDPR9B0B6A;User ID=sa;Password=Init1234').EasyHRO_ContinentalChina_20120221.[dbo].[emp_info] b
where a.emp_id=b.emp_id and a.emp_actid<>b.emp_actid
and a.emp_id=@emp_id
*/
END
