create FUNCTION dbo.v_usrlist
(
@usr varchar(20)/*传入当前用户代码*/
)
--返回动态表 表里面包含编码及名称两个字段
RETURNS
@tolist TABLE
(
usrcode varchar(30),
usrname varchar(64)
)
AS
BEGIN
--创建变量
declare @usrcattr varchar(30)
declare @usrgroup varchar(30)
declare @gpcattr varchar(30)
declare @orgcode varchar(30)
--获取传入账户对应的账户属性 群组信息
select @usrcattr=usrattr,@usrgroup=groups,@orgcode=orgcode from insuser where usrcode=@usr;
select @gpcattr = cattr from insgroups where groupno = @usrgroup;
--属性为管理员可查询所有用户
if (@usrcattr in (0,1,2))
begin
insert into @tolist select usrcode,usrname from insuser;
end
--属性为公司用户可查询公司内用户
if (@usrcattr = 3)
begin
insert into @tolist select usrcode,usrname from insuser where left(orgcode,5) = left(@orgcode,5);
end
--属性为部门用户可查询部门内用户
if (@usrcattr = 4)
begin
insert into @tolist sel