说明:
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