以前在sql里还没有调用过自定义函数,今天刚用过,所以想把他记录下来,以防以后忘记。
一、函数定义
--
将档案IDS的字符串转换成名字的字符串
ALTER FUNCTION GetArchivesNameStr(@SourceSql nvarchar(
500
),@StrSeprate nvarchar(
10
))
--
字符串格式41,
56
,
77
,
RETURNS nvarchar(
500
)
AS
begin
declare @names nvarchar(
500
)
declare @i
int
set
@names
=
''
set
@SourceSql
=
rtrim(ltrim(@SourceSql))
set
@i
=
charindex(@StrSeprate,@SourceSql)
while
@i
>=
1
begin
set
@names
=
@names
+
(select name from Archives where ArchivesID
=
convert(
int
,left(@SourceSql,@i
-
1
)))
+
'
,
'
set
@SourceSql
=
substring(@SourceSql,@i
+
1
,len(@SourceSql)
-
@i)
--
已经转换好的去掉
set
@i
=
charindex(@StrSeprate,@SourceSql)
--
重新定位
end
--
return
@names
if
@SourceSql
<>
''
and @i
>=
1
--
最后一个
begin
set
@names
=
@names
+
(select name from Archives where ArchivesID
=
convert(
int
,left(@SourceSql,@i
-
1
)))
end
if(right(@names,1)=',') --去掉后面的逗号
set @names=substring(@names,1,len(@names)-1)
return
@names
end
二、调用的函数
--
根据组织,岗位和档案ID返回该用户可见的日报列表
![](/Images/OutliningIndicators/None.gif)
ALTER proc dbo.GetDailyList @orgid
int
,@postid
int
,@ArchivesID
int
as
declare @a table(orgid
int
)
insert @a select orgid from organize where orgid
=
@orgid
while
@@rowcount
>
0
--
中间有一递归,有点难懂啊
insert @a select a.orgid from organize
as
a inner join @a
as
b
on a.fatherorgid
=
b.orgid and a.orgid not
in
(select orgid from @a)
![](/Images/OutliningIndicators/None.gif)
declare @b table(postid
int
)
insert @b select postid from post where postid
=
@postid
while
@@rowcount
>
0
insert @b select a.postid from post
as
a inner join @b
as
b
on a.fatherpostid
=
b.postid and a.postid not
in
(select postid from @b)
![](/Images/OutliningIndicators/None.gif)
--
declare @ArchivesID nvarchar(
20
)
--
select @ArchivesID
=
ArchivesID from Users where UserID
=
@userid
--
print @ArchivesID
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
SELECT a.DailyID, a.TaskTitle,a.AuthorID,b.Name
as
AuthorName,a.DispathchManID,
dbo.GetArchivesNameStr(a.DispathchManID,
'
,
'
) AS DispatchManName,
a.AddDate, a.hit,dbo.GetCommentCount(a.DailyID) AS commentcount,
'
StateStr
'
=
case
when a.StateID
=
1
then
'
未执行
'
when a.StateID
=
2
then
'
执行中
'
when a.StateID
=
3
then
'
执行完成
'
end,
'
ImportTypeName
'
=
case
when a.ImportTypeID
=
1
then
'
普通
'
when a.ImportTypeID
=
2
then
'
紧急
'
when a.ImportTypeID
=
3
then
'
非常紧急
'
end, dbo.GetArchivesNameStr(FinishManID,
'
,
'
)
as
FinishManName,
dbo.GetArchivesNameStr(FactDispatchManID,
'
,
'
)
as
FactDispatchManName
FROM DailyList a LEFT JOIN Archives b on a.AuthorID
=
b.ArchivesID
WHERE (a.DailyType
=
1
) AND
b.orgid IN (select orgid from @a) and
'
,
'
+
a.DispathchManID like
'
%,
'
+
convert(nvarchar,@ArchivesID)
+
'
,%
'
没想到函数的功能这么强大,可以方便好多工作啊,以后多用点,呵呵!