写公文流转流程监控时,为了监控传阅人员是否浏览阅读,采用了把用户ID保存成使用","分割开的字符串(varchar).在显示的时候就采用了两个function 控制.得到状态.感觉自己写的也不是很简练.需要再改善下.
function:
---------------------
CREATE FUNCTION stepString
(
@stepstring varchar(100)=null,
@gwlzid int
)
RETURNS varchar(500) AS
BEGIN
declare @returnstr varchar(500)
if(@stepstring =null)
begin
select @returnstr='未处理'
end
else
begin
if(patindex('%,%',@stepstring)=0)
begin ----只有一个
select @returnstr=dbo.gwlzuserinfo(@stepstring,@gwlzid)
end
if(patindex('%,%',@stepstring)>0)
begin
declare @tmpstr varchar(100)-----临时的被分离后剩下的字符
declare @tmpindex int ------临时的分离开始的位置
declare @tmpmaxlength int ----剩余字符的最大长度
select @tmpindex=0
select @tmpmaxlength=DATALENGTH(@stepstring)
select @tmpstr=@stepstring
select @returnstr=''
while (patindex('%,%',@tmpstr)>0)
begin
select @returnstr=@returnstr +dbo.gwlzuserinfo( substring(@tmpstr,1,patindex('%,%',@tmpstr)-1),@gwlzid)
select @tmpmaxlength=@tmpmaxlength-patindex('%,%',@tmpstr)
select @tmpstr=substring(@tmpstr,patindex('%,%',@tmpstr)+1,@tmpmaxlength)
end
select @returnstr=@returnstr + dbo.gwlzuserinfo(convert(int,@tmpstr),@gwlzid)
end
end
return @returnstr
END
--------------------------------
CREATE FUNCTION gwlzuserinfo
(
@userid int,
@gwlzid int
)
RETURNS varchar(50) AS
BEGIN
declare @returnstr varchar(50)
if exists(select * from oa_user where userid=@userid)
begin
select @returnstr=usern from oa_user where userid=@userid
if exists(select * from oa_gwlzswcy where swid=@gwlzid and userid=@userid)
begin
select @returnstr=@returnstr +':已阅
'
end
else
begin
select @returnstr=@returnstr +':未阅
'
end
end
else
begin
select @returnstr='未知用户'
end
return @returnstr
END
--------------------------------------------
主要是通过sql里的patindex和substring两个函数实现.