今天做了一个存储过程

可实现对人事基本资料表中的所有栏位进行check，若有变化，则显示出来

参数：emp_id

原理：在服务器上有两个数据库备份，对比 emp_info 这两张表中相同emp_id的记录 ，

若某一列值不同，则显示出来。

主要问题点：

1.若不能用 OPENDATASOURCE，则可以通过以下sql开启权限。

使用完成后，关闭Ad Hoc Distributed Queries：

 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