检查某员工是否在系统中操作单据

 /*
说明：
1.如果员工@empl_no在系统中有操作过数据，则此员工不能删除
2.判断是否操作过数据，通过变量表@t可以新增其他字段
  drop proc Proc_tbEmployee_delcheck
  declare @sign int
  exec Proc_tbEmployee_delcheck '003',@sign out
  select @sign

*/
create proc Proc_tbEmployee_delcheck
@empl_no varchar(50),@sign int out
as
set nocount on

declare @maxid int,@j int,@field varchar(30)
declare @t table(id int identity(1,1),field varchar(30))
insert into @t(field)
select 'empl_no'
union all select 'create_man'
union all select 'edit_man'
select @maxid=max(id) from @t

declare @flag int,@name varchar(100),@i int,@condition nvarchar(50)
declare @sql nvarchar(1000)
declare @cur cursor
set @flag=0

set @cur = cursor for select b.name
  from syscolumns a
  inner join sysobjects b on a.id=b.id
  where b.xtype='u' and b.name not in('tbRightMember','tbEmployee','tbLog','tbRight','tbRightAdd')
   and exists(select 1 from syscolumns c where a.id=c.id and a.name in(select field from @t))
open @cur
fetch next from @cur into @name
while @@fetch_status=0 and @flag=0
begin
 if exists(select 1 from syscolumns where id=object_id(@name) and name='deleted')
  set @condition=' and deleted=0 '
 else
  set @condition=''

 set @j=1
 while @j<=@maxid
 begin
  select @field=field from @t where id=@j
  if exists(select 1 from syscolumns where id=object_id(@name) and name=@field)
  begin
   set @sql='select @p=count(1) from '+@name+' where    if isnull(@i,0)>0
   begin
    set @flag=1
    break
   end
  end
  set @j=@j+1
 end
 fetch next from @cur into @name
end
close @cur
deallocate @cur
set @sign=@flag

 

GO