CREATE FUNCTION [dbo].[Fn_ShowDepartment]
(
@ID int
)
RETURNS varchar(500)
AS
BEGIN
Declare @Count int
Declare @Index int
Declare @Department varchar(200)
Declare @Mark varchar(500)
set @Count = (select count(*) from OA_GongGao_Detail where GGID = @ID)
set @Index=0
set @Department=''
set @Mark=''
while @Index < @Count
begin
set @Index = @Index+1
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 GG.GGID=@ID order by GG.ID
) a 
 order by ID desc)
[color=#FF0000]set @Mark = @Mark+@Department+','[/color]
end
if len(@Mark)>0
begin
set @Mark = left(@Mark,len(@Mark)-1)
end
return @Mark
END

Declare @Department varchar(200)
Declare @Mark varchar(500)
set @Mark=''
set @Department=''
select @Department='设计部'
set @Mark = @Mark+@Department+','
print @Mark

------解决方案--------------------
這樣也可以,
SQL code

 set @Mark = @Mark + case when @Department is null then '' else @Department+',' end