- SQL code
CREATE FUNCTION [dbo].[Fn_ShowDepartment](@ID int)RETURNS varchar(500)ASBEGINDeclare @Count intDeclare @Index intDeclare @Department varchar(200)Declare @Mark varchar(500)set @Count = (select count(*) from OA_GongGao_Detail where GGID = @ID)set @Index=0set @Department=''set @Mark=''while @Index < @Countbeginset @Index = @Index+1select @Department =(select top 1 DepartmentName from(select top(@Index) GG.ID as ID,DT.DepartmentName as DepartmentName,GG.ToDepartmentId as ToDepartmentId,GGID as GGID from OA_GongGao_Detail GG left join Sys_Department DT on GG.ToDepartmentId = DT.id where [email protected] order by GG.ID) a order by ID desc)[color=#FF0000]set @Mark = @[email protected]+','[/color]endif len(@Mark)>0beginset @Mark = left(@Mark,len(@Mark)-1)endreturn @MarkEND
set @Mark = @[email protected]+',' 这句怎么没用呢？？？
------解决方案--------------------
set @Count = (select count(*) from OA_GongGao_Detail where GGID = @ID)这条没有取到值
或者
select @Department =(select top 1 DepartmentName from
(
select top(@Index) GG.ID as ID,DT.DepartmentName as DepartmentName,GG.ToDepartmentId as ToDepartmentId,GGID as GGID
from OA_GongGao_Detail GG left join Sys_Department DT
on GG.ToDepartmentId = DT.id where [email protected] order by GG.ID
) a
order by ID desc) 没有取到值吧
因为我把这两条语的值赋为常量，会有结果出来的。
------解决方案--------------------
在set @Mark = @[email protected]+','[/color]
语句前面加上
print @Mark
print @Department
一下，看看是否有问题！。
------解决方案--------------------
- SQL code
Declare @Department varchar(200)Declare @Mark varchar(500)set @Mark=''set @Department=''select @Department='设计部'set @Mark = @[email protected]+','print @Mark
------解决方案--------------------
這樣也可以,
- SQL code
set @Mark = @Mark + case when @Department is null then '' else @Department+',' end